1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42
  43class _Expression(type):
  44    def __new__(cls, clsname, bases, attrs):
  45        klass = super().__new__(cls, clsname, bases, attrs)
  46
  47        # When an Expression class is created, its key is automatically set to be
  48        # the lowercase version of the class' name.
  49        klass.key = clsname.lower()
  50
  51        # This is so that docstrings are not inherited in pdoc
  52        klass.__doc__ = klass.__doc__ or ""
  53
  54        return klass
  55
  56
  57SQLGLOT_META = "sqlglot.meta"
  58TABLE_PARTS = ("this", "db", "catalog")
  59
  60
  61class Expression(metaclass=_Expression):
  62    """
  63    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  64    context, such as its child expressions, their names (arg keys), and whether a given child expression
  65    is optional or not.
  66
  67    Attributes:
  68        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  69            and representing expressions as strings.
  70        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  71            arg keys to booleans that indicate whether the corresponding args are optional.
  72        parent: a reference to the parent expression (or None, in case of root expressions).
  73        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  74            uses to refer to it.
  75        comments: a list of comments that are associated with a given expression. This is used in
  76            order to preserve comments when transpiling SQL code.
  77        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  78            optimizer, in order to enable some transformations that require type information.
  79        meta: a dictionary that can be used to store useful metadata for a given expression.
  80
  81    Example:
  82        >>> class Foo(Expression):
  83        ...     arg_types = {"this": True, "expression": False}
  84
  85        The above definition informs us that Foo is an Expression that requires an argument called
  86        "this" and may also optionally receive an argument called "expression".
  87
  88    Args:
  89        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  90    """
  91
  92    key = "expression"
  93    arg_types = {"this": True}
  94    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  95
  96    def __init__(self, **args: t.Any):
  97        self.args: t.Dict[str, t.Any] = args
  98        self.parent: t.Optional[Expression] = None
  99        self.arg_key: t.Optional[str] = None
 100        self.comments: t.Optional[t.List[str]] = None
 101        self._type: t.Optional[DataType] = None
 102        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 103        self._hash: t.Optional[int] = None
 104
 105        for arg_key, value in self.args.items():
 106            self._set_parent(arg_key, value)
 107
 108    def __eq__(self, other) -> bool:
 109        return type(self) is type(other) and hash(self) == hash(other)
 110
 111    @property
 112    def hashable_args(self) -> t.Any:
 113        return frozenset(
 114            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 115            for k, v in self.args.items()
 116            if not (v is None or v is False or (type(v) is list and not v))
 117        )
 118
 119    def __hash__(self) -> int:
 120        if self._hash is not None:
 121            return self._hash
 122
 123        return hash((self.__class__, self.hashable_args))
 124
 125    @property
 126    def this(self) -> t.Any:
 127        """
 128        Retrieves the argument with key "this".
 129        """
 130        return self.args.get("this")
 131
 132    @property
 133    def expression(self) -> t.Any:
 134        """
 135        Retrieves the argument with key "expression".
 136        """
 137        return self.args.get("expression")
 138
 139    @property
 140    def expressions(self) -> t.List[t.Any]:
 141        """
 142        Retrieves the argument with key "expressions".
 143        """
 144        return self.args.get("expressions") or []
 145
 146    def text(self, key) -> str:
 147        """
 148        Returns a textual representation of the argument corresponding to "key". This can only be used
 149        for args that are strings or leaf Expression instances, such as identifiers and literals.
 150        """
 151        field = self.args.get(key)
 152        if isinstance(field, str):
 153            return field
 154        if isinstance(field, (Identifier, Literal, Var)):
 155            return field.this
 156        if isinstance(field, (Star, Null)):
 157            return field.name
 158        return ""
 159
 160    @property
 161    def is_string(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a string.
 164        """
 165        return isinstance(self, Literal) and self.args["is_string"]
 166
 167    @property
 168    def is_number(self) -> bool:
 169        """
 170        Checks whether a Literal expression is a number.
 171        """
 172        return isinstance(self, Literal) and not self.args["is_string"]
 173
 174    @property
 175    def is_int(self) -> bool:
 176        """
 177        Checks whether a Literal expression is an integer.
 178        """
 179        return self.is_number and is_int(self.name)
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    def is_type(self, *dtypes) -> bool:
 239        return self.type is not None and self.type.is_type(*dtypes)
 240
 241    def is_leaf(self) -> bool:
 242        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return ancestor  # type: ignore
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes whose parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and type(n) is not self.__class__):
 492            if type(node) is not self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return _to_s(self)
 500
 501    def to_s(self) -> str:
 502        """
 503        Same as __repr__, but includes additional information which can be useful
 504        for debugging, like empty or missing args and the AST nodes' object IDs.
 505        """
 506        return _to_s(self, verbose=True)
 507
 508    def sql(self, dialect: DialectType = None, **opts) -> str:
 509        """
 510        Returns SQL string representation of this tree.
 511
 512        Args:
 513            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 514            opts: other `sqlglot.generator.Generator` options.
 515
 516        Returns:
 517            The SQL string.
 518        """
 519        from sqlglot.dialects import Dialect
 520
 521        return Dialect.get_or_raise(dialect).generate(self, **opts)
 522
 523    def transform(self, fun, *args, copy=True, **kwargs):
 524        """
 525        Recursively visits all tree nodes (excluding already transformed ones)
 526        and applies the given transformation function to each node.
 527
 528        Args:
 529            fun (function): a function which takes a node as an argument and returns a
 530                new transformed node or the same node without modifications. If the function
 531                returns None, then the corresponding node will be removed from the syntax tree.
 532            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 533                modified in place.
 534
 535        Returns:
 536            The transformed tree.
 537        """
 538        node = self.copy() if copy else self
 539        new_node = fun(node, *args, **kwargs)
 540
 541        if new_node is None or not isinstance(new_node, Expression):
 542            return new_node
 543        if new_node is not node:
 544            new_node.parent = node.parent
 545            return new_node
 546
 547        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 548        return new_node
 549
 550    @t.overload
 551    def replace(self, expression: E) -> E:
 552        ...
 553
 554    @t.overload
 555    def replace(self, expression: None) -> None:
 556        ...
 557
 558    def replace(self, expression):
 559        """
 560        Swap out this expression with a new expression.
 561
 562        For example::
 563
 564            >>> tree = Select().select("x").from_("tbl")
 565            >>> tree.find(Column).replace(column("y"))
 566            Column(
 567              this=Identifier(this=y, quoted=False))
 568            >>> tree.sql()
 569            'SELECT y FROM tbl'
 570
 571        Args:
 572            expression: new node
 573
 574        Returns:
 575            The new expression or expressions.
 576        """
 577        if not self.parent:
 578            return expression
 579
 580        parent = self.parent
 581        self.parent = None
 582
 583        replace_children(parent, lambda child: expression if child is self else child)
 584        return expression
 585
 586    def pop(self: E) -> E:
 587        """
 588        Remove this expression from its AST.
 589
 590        Returns:
 591            The popped expression.
 592        """
 593        self.replace(None)
 594        return self
 595
 596    def assert_is(self, type_: t.Type[E]) -> E:
 597        """
 598        Assert that this `Expression` is an instance of `type_`.
 599
 600        If it is NOT an instance of `type_`, this raises an assertion error.
 601        Otherwise, this returns this expression.
 602
 603        Examples:
 604            This is useful for type security in chained expressions:
 605
 606            >>> import sqlglot
 607            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 608            'SELECT x, z FROM y'
 609        """
 610        if not isinstance(self, type_):
 611            raise AssertionError(f"{self} is not {type_}.")
 612        return self
 613
 614    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 615        """
 616        Checks if this expression is valid (e.g. all mandatory args are set).
 617
 618        Args:
 619            args: a sequence of values that were used to instantiate a Func expression. This is used
 620                to check that the provided arguments don't exceed the function argument limit.
 621
 622        Returns:
 623            A list of error messages for all possible errors that were found.
 624        """
 625        errors: t.List[str] = []
 626
 627        for k in self.args:
 628            if k not in self.arg_types:
 629                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 630        for k, mandatory in self.arg_types.items():
 631            v = self.args.get(k)
 632            if mandatory and (v is None or (isinstance(v, list) and not v)):
 633                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 634
 635        if (
 636            args
 637            and isinstance(self, Func)
 638            and len(args) > len(self.arg_types)
 639            and not self.is_var_len_args
 640        ):
 641            errors.append(
 642                f"The number of provided arguments ({len(args)}) is greater than "
 643                f"the maximum number of supported arguments ({len(self.arg_types)})"
 644            )
 645
 646        return errors
 647
 648    def dump(self):
 649        """
 650        Dump this Expression to a JSON-serializable dict.
 651        """
 652        from sqlglot.serde import dump
 653
 654        return dump(self)
 655
 656    @classmethod
 657    def load(cls, obj):
 658        """
 659        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 660        """
 661        from sqlglot.serde import load
 662
 663        return load(obj)
 664
 665    def and_(
 666        self,
 667        *expressions: t.Optional[ExpOrStr],
 668        dialect: DialectType = None,
 669        copy: bool = True,
 670        **opts,
 671    ) -> Condition:
 672        """
 673        AND this condition with one or multiple expressions.
 674
 675        Example:
 676            >>> condition("x=1").and_("y=1").sql()
 677            'x = 1 AND y = 1'
 678
 679        Args:
 680            *expressions: the SQL code strings to parse.
 681                If an `Expression` instance is passed, it will be used as-is.
 682            dialect: the dialect used to parse the input expression.
 683            copy: whether to copy the involved expressions (only applies to Expressions).
 684            opts: other options to use to parse the input expressions.
 685
 686        Returns:
 687            The new And condition.
 688        """
 689        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 690
 691    def or_(
 692        self,
 693        *expressions: t.Optional[ExpOrStr],
 694        dialect: DialectType = None,
 695        copy: bool = True,
 696        **opts,
 697    ) -> Condition:
 698        """
 699        OR this condition with one or multiple expressions.
 700
 701        Example:
 702            >>> condition("x=1").or_("y=1").sql()
 703            'x = 1 OR y = 1'
 704
 705        Args:
 706            *expressions: the SQL code strings to parse.
 707                If an `Expression` instance is passed, it will be used as-is.
 708            dialect: the dialect used to parse the input expression.
 709            copy: whether to copy the involved expressions (only applies to Expressions).
 710            opts: other options to use to parse the input expressions.
 711
 712        Returns:
 713            The new Or condition.
 714        """
 715        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 716
 717    def not_(self, copy: bool = True):
 718        """
 719        Wrap this condition with NOT.
 720
 721        Example:
 722            >>> condition("x=1").not_().sql()
 723            'NOT x = 1'
 724
 725        Args:
 726            copy: whether to copy this object.
 727
 728        Returns:
 729            The new Not instance.
 730        """
 731        return not_(self, copy=copy)
 732
 733    def as_(
 734        self,
 735        alias: str | Identifier,
 736        quoted: t.Optional[bool] = None,
 737        dialect: DialectType = None,
 738        copy: bool = True,
 739        **opts,
 740    ) -> Alias:
 741        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 742
 743    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 744        this = self.copy()
 745        other = convert(other, copy=True)
 746        if not isinstance(this, klass) and not isinstance(other, klass):
 747            this = _wrap(this, Binary)
 748            other = _wrap(other, Binary)
 749        if reverse:
 750            return klass(this=other, expression=this)
 751        return klass(this=this, expression=other)
 752
 753    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 754        return Bracket(
 755            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 756        )
 757
 758    def __iter__(self) -> t.Iterator:
 759        if "expressions" in self.arg_types:
 760            return iter(self.args.get("expressions") or [])
 761        # We define this because __getitem__ converts Expression into an iterable, which is
 762        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 763        # See: https://peps.python.org/pep-0234/
 764        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 765
 766    def isin(
 767        self,
 768        *expressions: t.Any,
 769        query: t.Optional[ExpOrStr] = None,
 770        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 771        copy: bool = True,
 772        **opts,
 773    ) -> In:
 774        return In(
 775            this=maybe_copy(self, copy),
 776            expressions=[convert(e, copy=copy) for e in expressions],
 777            query=maybe_parse(query, copy=copy, **opts) if query else None,
 778            unnest=(
 779                Unnest(
 780                    expressions=[
 781                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 782                        for e in ensure_list(unnest)
 783                    ]
 784                )
 785                if unnest
 786                else None
 787            ),
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 816        div = self._binop(Div, other)
 817        div.args["typed"] = typed
 818        div.args["safe"] = safe
 819        return div
 820
 821    def desc(self, nulls_first: bool = False) -> Ordered:
 822        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Query) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Query(Expression):
 924    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
 925        """
 926        Returns a `Subquery` that wraps around this query.
 927
 928        Example:
 929            >>> subquery = Select().select("x").from_("tbl").subquery()
 930            >>> Select().select("x").from_(subquery).sql()
 931            'SELECT x FROM (SELECT x FROM tbl)'
 932
 933        Args:
 934            alias: an optional alias for the subquery.
 935            copy: if `False`, modify this expression instance in-place.
 936        """
 937        instance = maybe_copy(self, copy)
 938        if not isinstance(alias, Expression):
 939            alias = TableAlias(this=to_identifier(alias)) if alias else None
 940
 941        return Subquery(this=instance, alias=alias)
 942
 943    def limit(
 944        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
 945    ) -> Select:
 946        """
 947        Adds a LIMIT clause to this query.
 948
 949        Example:
 950            >>> select("1").union(select("1")).limit(1).sql()
 951            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
 952
 953        Args:
 954            expression: the SQL code string to parse.
 955                This can also be an integer.
 956                If a `Limit` instance is passed, it will be used as-is.
 957                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
 958            dialect: the dialect used to parse the input expression.
 959            copy: if `False`, modify this expression instance in-place.
 960            opts: other options to use to parse the input expressions.
 961
 962        Returns:
 963            A limited Select expression.
 964        """
 965        return (
 966            select("*")
 967            .from_(self.subquery(alias="_l_0", copy=copy))
 968            .limit(expression, dialect=dialect, copy=False, **opts)
 969        )
 970
 971    @property
 972    def ctes(self) -> t.List[CTE]:
 973        """Returns a list of all the CTEs attached to this query."""
 974        with_ = self.args.get("with")
 975        return with_.expressions if with_ else []
 976
 977    @property
 978    def selects(self) -> t.List[Expression]:
 979        """Returns the query's projections."""
 980        raise NotImplementedError("Query objects must implement `selects`")
 981
 982    @property
 983    def named_selects(self) -> t.List[str]:
 984        """Returns the output names of the query's projections."""
 985        raise NotImplementedError("Query objects must implement `named_selects`")
 986
 987    def select(
 988        self,
 989        *expressions: t.Optional[ExpOrStr],
 990        append: bool = True,
 991        dialect: DialectType = None,
 992        copy: bool = True,
 993        **opts,
 994    ) -> Query:
 995        """
 996        Append to or set the SELECT expressions.
 997
 998        Example:
 999            >>> Select().select("x", "y").sql()
1000            'SELECT x, y'
1001
1002        Args:
1003            *expressions: the SQL code strings to parse.
1004                If an `Expression` instance is passed, it will be used as-is.
1005            append: if `True`, add to any existing expressions.
1006                Otherwise, this resets the expressions.
1007            dialect: the dialect used to parse the input expressions.
1008            copy: if `False`, modify this expression instance in-place.
1009            opts: other options to use to parse the input expressions.
1010
1011        Returns:
1012            The modified Query expression.
1013        """
1014        raise NotImplementedError("Query objects must implement `select`")
1015
1016    def with_(
1017        self,
1018        alias: ExpOrStr,
1019        as_: ExpOrStr,
1020        recursive: t.Optional[bool] = None,
1021        append: bool = True,
1022        dialect: DialectType = None,
1023        copy: bool = True,
1024        **opts,
1025    ) -> Query:
1026        """
1027        Append to or set the common table expressions.
1028
1029        Example:
1030            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1031            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1032
1033        Args:
1034            alias: the SQL code string to parse as the table name.
1035                If an `Expression` instance is passed, this is used as-is.
1036            as_: the SQL code string to parse as the table expression.
1037                If an `Expression` instance is passed, it will be used as-is.
1038            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1039            append: if `True`, add to any existing expressions.
1040                Otherwise, this resets the expressions.
1041            dialect: the dialect used to parse the input expression.
1042            copy: if `False`, modify this expression instance in-place.
1043            opts: other options to use to parse the input expressions.
1044
1045        Returns:
1046            The modified expression.
1047        """
1048        return _apply_cte_builder(
1049            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1050        )
1051
1052    def union(
1053        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1054    ) -> Union:
1055        """
1056        Builds a UNION expression.
1057
1058        Example:
1059            >>> import sqlglot
1060            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1061            'SELECT * FROM foo UNION SELECT * FROM bla'
1062
1063        Args:
1064            expression: the SQL code string.
1065                If an `Expression` instance is passed, it will be used as-is.
1066            distinct: set the DISTINCT flag if and only if this is true.
1067            dialect: the dialect used to parse the input expression.
1068            opts: other options to use to parse the input expressions.
1069
1070        Returns:
1071            The new Union expression.
1072        """
1073        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1074
1075    def intersect(
1076        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1077    ) -> Intersect:
1078        """
1079        Builds an INTERSECT expression.
1080
1081        Example:
1082            >>> import sqlglot
1083            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1084            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1085
1086        Args:
1087            expression: the SQL code string.
1088                If an `Expression` instance is passed, it will be used as-is.
1089            distinct: set the DISTINCT flag if and only if this is true.
1090            dialect: the dialect used to parse the input expression.
1091            opts: other options to use to parse the input expressions.
1092
1093        Returns:
1094            The new Intersect expression.
1095        """
1096        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1097
1098    def except_(
1099        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1100    ) -> Except:
1101        """
1102        Builds an EXCEPT expression.
1103
1104        Example:
1105            >>> import sqlglot
1106            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1107            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1108
1109        Args:
1110            expression: the SQL code string.
1111                If an `Expression` instance is passed, it will be used as-is.
1112            distinct: set the DISTINCT flag if and only if this is true.
1113            dialect: the dialect used to parse the input expression.
1114            opts: other options to use to parse the input expressions.
1115
1116        Returns:
1117            The new Except expression.
1118        """
1119        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1120
1121
1122class UDTF(DerivedTable):
1123    @property
1124    def selects(self) -> t.List[Expression]:
1125        alias = self.args.get("alias")
1126        return alias.columns if alias else []
1127
1128
1129class Cache(Expression):
1130    arg_types = {
1131        "this": True,
1132        "lazy": False,
1133        "options": False,
1134        "expression": False,
1135    }
1136
1137
1138class Uncache(Expression):
1139    arg_types = {"this": True, "exists": False}
1140
1141
1142class Refresh(Expression):
1143    pass
1144
1145
1146class DDL(Expression):
1147    @property
1148    def ctes(self) -> t.List[CTE]:
1149        """Returns a list of all the CTEs attached to this statement."""
1150        with_ = self.args.get("with")
1151        return with_.expressions if with_ else []
1152
1153    @property
1154    def selects(self) -> t.List[Expression]:
1155        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1156        return self.expression.selects if isinstance(self.expression, Query) else []
1157
1158    @property
1159    def named_selects(self) -> t.List[str]:
1160        """
1161        If this statement contains a query (e.g. a CTAS), this returns the output
1162        names of the query's projections.
1163        """
1164        return self.expression.named_selects if isinstance(self.expression, Query) else []
1165
1166
1167class DML(Expression):
1168    def returning(
1169        self,
1170        expression: ExpOrStr,
1171        dialect: DialectType = None,
1172        copy: bool = True,
1173        **opts,
1174    ) -> DML:
1175        """
1176        Set the RETURNING expression. Not supported by all dialects.
1177
1178        Example:
1179            >>> delete("tbl").returning("*", dialect="postgres").sql()
1180            'DELETE FROM tbl RETURNING *'
1181
1182        Args:
1183            expression: the SQL code strings to parse.
1184                If an `Expression` instance is passed, it will be used as-is.
1185            dialect: the dialect used to parse the input expressions.
1186            copy: if `False`, modify this expression instance in-place.
1187            opts: other options to use to parse the input expressions.
1188
1189        Returns:
1190            Delete: the modified expression.
1191        """
1192        return _apply_builder(
1193            expression=expression,
1194            instance=self,
1195            arg="returning",
1196            prefix="RETURNING",
1197            dialect=dialect,
1198            copy=copy,
1199            into=Returning,
1200            **opts,
1201        )
1202
1203
1204class Create(DDL):
1205    arg_types = {
1206        "with": False,
1207        "this": True,
1208        "kind": True,
1209        "expression": False,
1210        "exists": False,
1211        "properties": False,
1212        "replace": False,
1213        "unique": False,
1214        "indexes": False,
1215        "no_schema_binding": False,
1216        "begin": False,
1217        "end": False,
1218        "clone": False,
1219    }
1220
1221    @property
1222    def kind(self) -> t.Optional[str]:
1223        kind = self.args.get("kind")
1224        return kind and kind.upper()
1225
1226
1227# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1228# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1229# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1230class Clone(Expression):
1231    arg_types = {"this": True, "shallow": False, "copy": False}
1232
1233
1234class Describe(Expression):
1235    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1236
1237
1238class Kill(Expression):
1239    arg_types = {"this": True, "kind": False}
1240
1241
1242class Pragma(Expression):
1243    pass
1244
1245
1246class Set(Expression):
1247    arg_types = {"expressions": False, "unset": False, "tag": False}
1248
1249
1250class Heredoc(Expression):
1251    arg_types = {"this": True, "tag": False}
1252
1253
1254class SetItem(Expression):
1255    arg_types = {
1256        "this": False,
1257        "expressions": False,
1258        "kind": False,
1259        "collate": False,  # MySQL SET NAMES statement
1260        "global": False,
1261    }
1262
1263
1264class Show(Expression):
1265    arg_types = {
1266        "this": True,
1267        "history": False,
1268        "terse": False,
1269        "target": False,
1270        "offset": False,
1271        "starts_with": False,
1272        "limit": False,
1273        "from": False,
1274        "like": False,
1275        "where": False,
1276        "db": False,
1277        "scope": False,
1278        "scope_kind": False,
1279        "full": False,
1280        "mutex": False,
1281        "query": False,
1282        "channel": False,
1283        "global": False,
1284        "log": False,
1285        "position": False,
1286        "types": False,
1287    }
1288
1289
1290class UserDefinedFunction(Expression):
1291    arg_types = {"this": True, "expressions": False, "wrapped": False}
1292
1293
1294class CharacterSet(Expression):
1295    arg_types = {"this": True, "default": False}
1296
1297
1298class With(Expression):
1299    arg_types = {"expressions": True, "recursive": False}
1300
1301    @property
1302    def recursive(self) -> bool:
1303        return bool(self.args.get("recursive"))
1304
1305
1306class WithinGroup(Expression):
1307    arg_types = {"this": True, "expression": False}
1308
1309
1310# clickhouse supports scalar ctes
1311# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1312class CTE(DerivedTable):
1313    arg_types = {"this": True, "alias": True, "scalar": False}
1314
1315
1316class TableAlias(Expression):
1317    arg_types = {"this": False, "columns": False}
1318
1319    @property
1320    def columns(self):
1321        return self.args.get("columns") or []
1322
1323
1324class BitString(Condition):
1325    pass
1326
1327
1328class HexString(Condition):
1329    pass
1330
1331
1332class ByteString(Condition):
1333    pass
1334
1335
1336class RawString(Condition):
1337    pass
1338
1339
1340class UnicodeString(Condition):
1341    arg_types = {"this": True, "escape": False}
1342
1343
1344class Column(Condition):
1345    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1346
1347    @property
1348    def table(self) -> str:
1349        return self.text("table")
1350
1351    @property
1352    def db(self) -> str:
1353        return self.text("db")
1354
1355    @property
1356    def catalog(self) -> str:
1357        return self.text("catalog")
1358
1359    @property
1360    def output_name(self) -> str:
1361        return self.name
1362
1363    @property
1364    def parts(self) -> t.List[Identifier]:
1365        """Return the parts of a column in order catalog, db, table, name."""
1366        return [
1367            t.cast(Identifier, self.args[part])
1368            for part in ("catalog", "db", "table", "this")
1369            if self.args.get(part)
1370        ]
1371
1372    def to_dot(self) -> Dot | Identifier:
1373        """Converts the column into a dot expression."""
1374        parts = self.parts
1375        parent = self.parent
1376
1377        while parent:
1378            if isinstance(parent, Dot):
1379                parts.append(parent.expression)
1380            parent = parent.parent
1381
1382        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1383
1384
1385class ColumnPosition(Expression):
1386    arg_types = {"this": False, "position": True}
1387
1388
1389class ColumnDef(Expression):
1390    arg_types = {
1391        "this": True,
1392        "kind": False,
1393        "constraints": False,
1394        "exists": False,
1395        "position": False,
1396    }
1397
1398    @property
1399    def constraints(self) -> t.List[ColumnConstraint]:
1400        return self.args.get("constraints") or []
1401
1402    @property
1403    def kind(self) -> t.Optional[DataType]:
1404        return self.args.get("kind")
1405
1406
1407class AlterColumn(Expression):
1408    arg_types = {
1409        "this": True,
1410        "dtype": False,
1411        "collate": False,
1412        "using": False,
1413        "default": False,
1414        "drop": False,
1415        "comment": False,
1416    }
1417
1418
1419class RenameColumn(Expression):
1420    arg_types = {"this": True, "to": True, "exists": False}
1421
1422
1423class RenameTable(Expression):
1424    pass
1425
1426
1427class SwapTable(Expression):
1428    pass
1429
1430
1431class Comment(Expression):
1432    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1433
1434
1435class Comprehension(Expression):
1436    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1437
1438
1439# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1440class MergeTreeTTLAction(Expression):
1441    arg_types = {
1442        "this": True,
1443        "delete": False,
1444        "recompress": False,
1445        "to_disk": False,
1446        "to_volume": False,
1447    }
1448
1449
1450# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1451class MergeTreeTTL(Expression):
1452    arg_types = {
1453        "expressions": True,
1454        "where": False,
1455        "group": False,
1456        "aggregates": False,
1457    }
1458
1459
1460# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1461class IndexConstraintOption(Expression):
1462    arg_types = {
1463        "key_block_size": False,
1464        "using": False,
1465        "parser": False,
1466        "comment": False,
1467        "visible": False,
1468        "engine_attr": False,
1469        "secondary_engine_attr": False,
1470    }
1471
1472
1473class ColumnConstraint(Expression):
1474    arg_types = {"this": False, "kind": True}
1475
1476    @property
1477    def kind(self) -> ColumnConstraintKind:
1478        return self.args["kind"]
1479
1480
1481class ColumnConstraintKind(Expression):
1482    pass
1483
1484
1485class AutoIncrementColumnConstraint(ColumnConstraintKind):
1486    pass
1487
1488
1489class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1490    arg_types = {"this": True, "expression": True}
1491
1492
1493class CaseSpecificColumnConstraint(ColumnConstraintKind):
1494    arg_types = {"not_": True}
1495
1496
1497class CharacterSetColumnConstraint(ColumnConstraintKind):
1498    arg_types = {"this": True}
1499
1500
1501class CheckColumnConstraint(ColumnConstraintKind):
1502    arg_types = {"this": True, "enforced": False}
1503
1504
1505class ClusteredColumnConstraint(ColumnConstraintKind):
1506    pass
1507
1508
1509class CollateColumnConstraint(ColumnConstraintKind):
1510    pass
1511
1512
1513class CommentColumnConstraint(ColumnConstraintKind):
1514    pass
1515
1516
1517class CompressColumnConstraint(ColumnConstraintKind):
1518    pass
1519
1520
1521class DateFormatColumnConstraint(ColumnConstraintKind):
1522    arg_types = {"this": True}
1523
1524
1525class DefaultColumnConstraint(ColumnConstraintKind):
1526    pass
1527
1528
1529class EncodeColumnConstraint(ColumnConstraintKind):
1530    pass
1531
1532
1533class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1534    # this: True -> ALWAYS, this: False -> BY DEFAULT
1535    arg_types = {
1536        "this": False,
1537        "expression": False,
1538        "on_null": False,
1539        "start": False,
1540        "increment": False,
1541        "minvalue": False,
1542        "maxvalue": False,
1543        "cycle": False,
1544    }
1545
1546
1547class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1548    arg_types = {"start": False, "hidden": False}
1549
1550
1551# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1552class IndexColumnConstraint(ColumnConstraintKind):
1553    arg_types = {
1554        "this": False,
1555        "schema": True,
1556        "kind": False,
1557        "index_type": False,
1558        "options": False,
1559    }
1560
1561
1562class InlineLengthColumnConstraint(ColumnConstraintKind):
1563    pass
1564
1565
1566class NonClusteredColumnConstraint(ColumnConstraintKind):
1567    pass
1568
1569
1570class NotForReplicationColumnConstraint(ColumnConstraintKind):
1571    arg_types = {}
1572
1573
1574class NotNullColumnConstraint(ColumnConstraintKind):
1575    arg_types = {"allow_null": False}
1576
1577
1578# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1579class OnUpdateColumnConstraint(ColumnConstraintKind):
1580    pass
1581
1582
1583# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1584class TransformColumnConstraint(ColumnConstraintKind):
1585    pass
1586
1587
1588class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1589    arg_types = {"desc": False}
1590
1591
1592class TitleColumnConstraint(ColumnConstraintKind):
1593    pass
1594
1595
1596class UniqueColumnConstraint(ColumnConstraintKind):
1597    arg_types = {"this": False, "index_type": False}
1598
1599
1600class UppercaseColumnConstraint(ColumnConstraintKind):
1601    arg_types: t.Dict[str, t.Any] = {}
1602
1603
1604class PathColumnConstraint(ColumnConstraintKind):
1605    pass
1606
1607
1608# computed column expression
1609# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1610class ComputedColumnConstraint(ColumnConstraintKind):
1611    arg_types = {"this": True, "persisted": False, "not_null": False}
1612
1613
1614class Constraint(Expression):
1615    arg_types = {"this": True, "expressions": True}
1616
1617
1618class Delete(DML):
1619    arg_types = {
1620        "with": False,
1621        "this": False,
1622        "using": False,
1623        "where": False,
1624        "returning": False,
1625        "limit": False,
1626        "tables": False,  # Multiple-Table Syntax (MySQL)
1627    }
1628
1629    def delete(
1630        self,
1631        table: ExpOrStr,
1632        dialect: DialectType = None,
1633        copy: bool = True,
1634        **opts,
1635    ) -> Delete:
1636        """
1637        Create a DELETE expression or replace the table on an existing DELETE expression.
1638
1639        Example:
1640            >>> delete("tbl").sql()
1641            'DELETE FROM tbl'
1642
1643        Args:
1644            table: the table from which to delete.
1645            dialect: the dialect used to parse the input expression.
1646            copy: if `False`, modify this expression instance in-place.
1647            opts: other options to use to parse the input expressions.
1648
1649        Returns:
1650            Delete: the modified expression.
1651        """
1652        return _apply_builder(
1653            expression=table,
1654            instance=self,
1655            arg="this",
1656            dialect=dialect,
1657            into=Table,
1658            copy=copy,
1659            **opts,
1660        )
1661
1662    def where(
1663        self,
1664        *expressions: t.Optional[ExpOrStr],
1665        append: bool = True,
1666        dialect: DialectType = None,
1667        copy: bool = True,
1668        **opts,
1669    ) -> Delete:
1670        """
1671        Append to or set the WHERE expressions.
1672
1673        Example:
1674            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1675            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1676
1677        Args:
1678            *expressions: the SQL code strings to parse.
1679                If an `Expression` instance is passed, it will be used as-is.
1680                Multiple expressions are combined with an AND operator.
1681            append: if `True`, AND the new expressions to any existing expression.
1682                Otherwise, this resets the expression.
1683            dialect: the dialect used to parse the input expressions.
1684            copy: if `False`, modify this expression instance in-place.
1685            opts: other options to use to parse the input expressions.
1686
1687        Returns:
1688            Delete: the modified expression.
1689        """
1690        return _apply_conjunction_builder(
1691            *expressions,
1692            instance=self,
1693            arg="where",
1694            append=append,
1695            into=Where,
1696            dialect=dialect,
1697            copy=copy,
1698            **opts,
1699        )
1700
1701
1702class Drop(Expression):
1703    arg_types = {
1704        "this": False,
1705        "kind": False,
1706        "exists": False,
1707        "temporary": False,
1708        "materialized": False,
1709        "cascade": False,
1710        "constraints": False,
1711        "purge": False,
1712    }
1713
1714
1715class Filter(Expression):
1716    arg_types = {"this": True, "expression": True}
1717
1718
1719class Check(Expression):
1720    pass
1721
1722
1723# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1724class Connect(Expression):
1725    arg_types = {"start": False, "connect": True}
1726
1727
1728class Prior(Expression):
1729    pass
1730
1731
1732class Directory(Expression):
1733    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1734    arg_types = {"this": True, "local": False, "row_format": False}
1735
1736
1737class ForeignKey(Expression):
1738    arg_types = {
1739        "expressions": True,
1740        "reference": False,
1741        "delete": False,
1742        "update": False,
1743    }
1744
1745
1746class ColumnPrefix(Expression):
1747    arg_types = {"this": True, "expression": True}
1748
1749
1750class PrimaryKey(Expression):
1751    arg_types = {"expressions": True, "options": False}
1752
1753
1754# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1755# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1756class Into(Expression):
1757    arg_types = {"this": True, "temporary": False, "unlogged": False}
1758
1759
1760class From(Expression):
1761    @property
1762    def name(self) -> str:
1763        return self.this.name
1764
1765    @property
1766    def alias_or_name(self) -> str:
1767        return self.this.alias_or_name
1768
1769
1770class Having(Expression):
1771    pass
1772
1773
1774class Hint(Expression):
1775    arg_types = {"expressions": True}
1776
1777
1778class JoinHint(Expression):
1779    arg_types = {"this": True, "expressions": True}
1780
1781
1782class Identifier(Expression):
1783    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1784
1785    @property
1786    def quoted(self) -> bool:
1787        return bool(self.args.get("quoted"))
1788
1789    @property
1790    def hashable_args(self) -> t.Any:
1791        return (self.this, self.quoted)
1792
1793    @property
1794    def output_name(self) -> str:
1795        return self.name
1796
1797
1798# https://www.postgresql.org/docs/current/indexes-opclass.html
1799class Opclass(Expression):
1800    arg_types = {"this": True, "expression": True}
1801
1802
1803class Index(Expression):
1804    arg_types = {
1805        "this": False,
1806        "table": False,
1807        "using": False,
1808        "where": False,
1809        "columns": False,
1810        "unique": False,
1811        "primary": False,
1812        "amp": False,  # teradata
1813        "include": False,
1814        "partition_by": False,  # teradata
1815    }
1816
1817
1818class Insert(DDL, DML):
1819    arg_types = {
1820        "with": False,
1821        "this": True,
1822        "expression": False,
1823        "conflict": False,
1824        "returning": False,
1825        "overwrite": False,
1826        "exists": False,
1827        "partition": False,
1828        "alternative": False,
1829        "where": False,
1830        "ignore": False,
1831        "by_name": False,
1832    }
1833
1834    def with_(
1835        self,
1836        alias: ExpOrStr,
1837        as_: ExpOrStr,
1838        recursive: t.Optional[bool] = None,
1839        append: bool = True,
1840        dialect: DialectType = None,
1841        copy: bool = True,
1842        **opts,
1843    ) -> Insert:
1844        """
1845        Append to or set the common table expressions.
1846
1847        Example:
1848            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1849            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1850
1851        Args:
1852            alias: the SQL code string to parse as the table name.
1853                If an `Expression` instance is passed, this is used as-is.
1854            as_: the SQL code string to parse as the table expression.
1855                If an `Expression` instance is passed, it will be used as-is.
1856            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1857            append: if `True`, add to any existing expressions.
1858                Otherwise, this resets the expressions.
1859            dialect: the dialect used to parse the input expression.
1860            copy: if `False`, modify this expression instance in-place.
1861            opts: other options to use to parse the input expressions.
1862
1863        Returns:
1864            The modified expression.
1865        """
1866        return _apply_cte_builder(
1867            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1868        )
1869
1870
1871class OnConflict(Expression):
1872    arg_types = {
1873        "duplicate": False,
1874        "expressions": False,
1875        "nothing": False,
1876        "key": False,
1877        "constraint": False,
1878    }
1879
1880
1881class Returning(Expression):
1882    arg_types = {"expressions": True, "into": False}
1883
1884
1885# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1886class Introducer(Expression):
1887    arg_types = {"this": True, "expression": True}
1888
1889
1890# national char, like n'utf8'
1891class National(Expression):
1892    pass
1893
1894
1895class LoadData(Expression):
1896    arg_types = {
1897        "this": True,
1898        "local": False,
1899        "overwrite": False,
1900        "inpath": True,
1901        "partition": False,
1902        "input_format": False,
1903        "serde": False,
1904    }
1905
1906
1907class Partition(Expression):
1908    arg_types = {"expressions": True}
1909
1910
1911class Fetch(Expression):
1912    arg_types = {
1913        "direction": False,
1914        "count": False,
1915        "percent": False,
1916        "with_ties": False,
1917    }
1918
1919
1920class Group(Expression):
1921    arg_types = {
1922        "expressions": False,
1923        "grouping_sets": False,
1924        "cube": False,
1925        "rollup": False,
1926        "totals": False,
1927        "all": False,
1928    }
1929
1930
1931class Lambda(Expression):
1932    arg_types = {"this": True, "expressions": True}
1933
1934
1935class Limit(Expression):
1936    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
1937
1938
1939class Literal(Condition):
1940    arg_types = {"this": True, "is_string": True}
1941
1942    @property
1943    def hashable_args(self) -> t.Any:
1944        return (self.this, self.args.get("is_string"))
1945
1946    @classmethod
1947    def number(cls, number) -> Literal:
1948        return cls(this=str(number), is_string=False)
1949
1950    @classmethod
1951    def string(cls, string) -> Literal:
1952        return cls(this=str(string), is_string=True)
1953
1954    @property
1955    def output_name(self) -> str:
1956        return self.name
1957
1958
1959class Join(Expression):
1960    arg_types = {
1961        "this": True,
1962        "on": False,
1963        "side": False,
1964        "kind": False,
1965        "using": False,
1966        "method": False,
1967        "global": False,
1968        "hint": False,
1969    }
1970
1971    @property
1972    def method(self) -> str:
1973        return self.text("method").upper()
1974
1975    @property
1976    def kind(self) -> str:
1977        return self.text("kind").upper()
1978
1979    @property
1980    def side(self) -> str:
1981        return self.text("side").upper()
1982
1983    @property
1984    def hint(self) -> str:
1985        return self.text("hint").upper()
1986
1987    @property
1988    def alias_or_name(self) -> str:
1989        return self.this.alias_or_name
1990
1991    def on(
1992        self,
1993        *expressions: t.Optional[ExpOrStr],
1994        append: bool = True,
1995        dialect: DialectType = None,
1996        copy: bool = True,
1997        **opts,
1998    ) -> Join:
1999        """
2000        Append to or set the ON expressions.
2001
2002        Example:
2003            >>> import sqlglot
2004            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2005            'JOIN x ON y = 1'
2006
2007        Args:
2008            *expressions: the SQL code strings to parse.
2009                If an `Expression` instance is passed, it will be used as-is.
2010                Multiple expressions are combined with an AND operator.
2011            append: if `True`, AND the new expressions to any existing expression.
2012                Otherwise, this resets the expression.
2013            dialect: the dialect used to parse the input expressions.
2014            copy: if `False`, modify this expression instance in-place.
2015            opts: other options to use to parse the input expressions.
2016
2017        Returns:
2018            The modified Join expression.
2019        """
2020        join = _apply_conjunction_builder(
2021            *expressions,
2022            instance=self,
2023            arg="on",
2024            append=append,
2025            dialect=dialect,
2026            copy=copy,
2027            **opts,
2028        )
2029
2030        if join.kind == "CROSS":
2031            join.set("kind", None)
2032
2033        return join
2034
2035    def using(
2036        self,
2037        *expressions: t.Optional[ExpOrStr],
2038        append: bool = True,
2039        dialect: DialectType = None,
2040        copy: bool = True,
2041        **opts,
2042    ) -> Join:
2043        """
2044        Append to or set the USING expressions.
2045
2046        Example:
2047            >>> import sqlglot
2048            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2049            'JOIN x USING (foo, bla)'
2050
2051        Args:
2052            *expressions: the SQL code strings to parse.
2053                If an `Expression` instance is passed, it will be used as-is.
2054            append: if `True`, concatenate the new expressions to the existing "using" list.
2055                Otherwise, this resets the expression.
2056            dialect: the dialect used to parse the input expressions.
2057            copy: if `False`, modify this expression instance in-place.
2058            opts: other options to use to parse the input expressions.
2059
2060        Returns:
2061            The modified Join expression.
2062        """
2063        join = _apply_list_builder(
2064            *expressions,
2065            instance=self,
2066            arg="using",
2067            append=append,
2068            dialect=dialect,
2069            copy=copy,
2070            **opts,
2071        )
2072
2073        if join.kind == "CROSS":
2074            join.set("kind", None)
2075
2076        return join
2077
2078
2079class Lateral(UDTF):
2080    arg_types = {
2081        "this": True,
2082        "view": False,
2083        "outer": False,
2084        "alias": False,
2085        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2086    }
2087
2088
2089class MatchRecognize(Expression):
2090    arg_types = {
2091        "partition_by": False,
2092        "order": False,
2093        "measures": False,
2094        "rows": False,
2095        "after": False,
2096        "pattern": False,
2097        "define": False,
2098        "alias": False,
2099    }
2100
2101
2102# Clickhouse FROM FINAL modifier
2103# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2104class Final(Expression):
2105    pass
2106
2107
2108class Offset(Expression):
2109    arg_types = {"this": False, "expression": True, "expressions": False}
2110
2111
2112class Order(Expression):
2113    arg_types = {
2114        "this": False,
2115        "expressions": True,
2116        "interpolate": False,
2117        "siblings": False,
2118    }
2119
2120
2121# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2122class WithFill(Expression):
2123    arg_types = {"from": False, "to": False, "step": False}
2124
2125
2126# hive specific sorts
2127# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2128class Cluster(Order):
2129    pass
2130
2131
2132class Distribute(Order):
2133    pass
2134
2135
2136class Sort(Order):
2137    pass
2138
2139
2140class Ordered(Expression):
2141    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2142
2143
2144class Property(Expression):
2145    arg_types = {"this": True, "value": True}
2146
2147
2148class AlgorithmProperty(Property):
2149    arg_types = {"this": True}
2150
2151
2152class AutoIncrementProperty(Property):
2153    arg_types = {"this": True}
2154
2155
2156# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2157class AutoRefreshProperty(Property):
2158    arg_types = {"this": True}
2159
2160
2161class BlockCompressionProperty(Property):
2162    arg_types = {
2163        "autotemp": False,
2164        "always": False,
2165        "default": False,
2166        "manual": False,
2167        "never": False,
2168    }
2169
2170
2171class CharacterSetProperty(Property):
2172    arg_types = {"this": True, "default": True}
2173
2174
2175class ChecksumProperty(Property):
2176    arg_types = {"on": False, "default": False}
2177
2178
2179class CollateProperty(Property):
2180    arg_types = {"this": True, "default": False}
2181
2182
2183class CopyGrantsProperty(Property):
2184    arg_types = {}
2185
2186
2187class DataBlocksizeProperty(Property):
2188    arg_types = {
2189        "size": False,
2190        "units": False,
2191        "minimum": False,
2192        "maximum": False,
2193        "default": False,
2194    }
2195
2196
2197class DefinerProperty(Property):
2198    arg_types = {"this": True}
2199
2200
2201class DistKeyProperty(Property):
2202    arg_types = {"this": True}
2203
2204
2205class DistStyleProperty(Property):
2206    arg_types = {"this": True}
2207
2208
2209class EngineProperty(Property):
2210    arg_types = {"this": True}
2211
2212
2213class HeapProperty(Property):
2214    arg_types = {}
2215
2216
2217class ToTableProperty(Property):
2218    arg_types = {"this": True}
2219
2220
2221class ExecuteAsProperty(Property):
2222    arg_types = {"this": True}
2223
2224
2225class ExternalProperty(Property):
2226    arg_types = {"this": False}
2227
2228
2229class FallbackProperty(Property):
2230    arg_types = {"no": True, "protection": False}
2231
2232
2233class FileFormatProperty(Property):
2234    arg_types = {"this": True}
2235
2236
2237class FreespaceProperty(Property):
2238    arg_types = {"this": True, "percent": False}
2239
2240
2241class InheritsProperty(Property):
2242    arg_types = {"expressions": True}
2243
2244
2245class InputModelProperty(Property):
2246    arg_types = {"this": True}
2247
2248
2249class OutputModelProperty(Property):
2250    arg_types = {"this": True}
2251
2252
2253class IsolatedLoadingProperty(Property):
2254    arg_types = {
2255        "no": False,
2256        "concurrent": False,
2257        "for_all": False,
2258        "for_insert": False,
2259        "for_none": False,
2260    }
2261
2262
2263class JournalProperty(Property):
2264    arg_types = {
2265        "no": False,
2266        "dual": False,
2267        "before": False,
2268        "local": False,
2269        "after": False,
2270    }
2271
2272
2273class LanguageProperty(Property):
2274    arg_types = {"this": True}
2275
2276
2277# spark ddl
2278class ClusteredByProperty(Property):
2279    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2280
2281
2282class DictProperty(Property):
2283    arg_types = {"this": True, "kind": True, "settings": False}
2284
2285
2286class DictSubProperty(Property):
2287    pass
2288
2289
2290class DictRange(Property):
2291    arg_types = {"this": True, "min": True, "max": True}
2292
2293
2294# Clickhouse CREATE ... ON CLUSTER modifier
2295# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2296class OnCluster(Property):
2297    arg_types = {"this": True}
2298
2299
2300class LikeProperty(Property):
2301    arg_types = {"this": True, "expressions": False}
2302
2303
2304class LocationProperty(Property):
2305    arg_types = {"this": True}
2306
2307
2308class LockingProperty(Property):
2309    arg_types = {
2310        "this": False,
2311        "kind": True,
2312        "for_or_in": False,
2313        "lock_type": True,
2314        "override": False,
2315    }
2316
2317
2318class LogProperty(Property):
2319    arg_types = {"no": True}
2320
2321
2322class MaterializedProperty(Property):
2323    arg_types = {"this": False}
2324
2325
2326class MergeBlockRatioProperty(Property):
2327    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2328
2329
2330class NoPrimaryIndexProperty(Property):
2331    arg_types = {}
2332
2333
2334class OnProperty(Property):
2335    arg_types = {"this": True}
2336
2337
2338class OnCommitProperty(Property):
2339    arg_types = {"delete": False}
2340
2341
2342class PartitionedByProperty(Property):
2343    arg_types = {"this": True}
2344
2345
2346# https://www.postgresql.org/docs/current/sql-createtable.html
2347class PartitionBoundSpec(Expression):
2348    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2349    arg_types = {
2350        "this": False,
2351        "expression": False,
2352        "from_expressions": False,
2353        "to_expressions": False,
2354    }
2355
2356
2357class PartitionedOfProperty(Property):
2358    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2359    arg_types = {"this": True, "expression": True}
2360
2361
2362class RemoteWithConnectionModelProperty(Property):
2363    arg_types = {"this": True}
2364
2365
2366class ReturnsProperty(Property):
2367    arg_types = {"this": True, "is_table": False, "table": False}
2368
2369
2370class RowFormatProperty(Property):
2371    arg_types = {"this": True}
2372
2373
2374class RowFormatDelimitedProperty(Property):
2375    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2376    arg_types = {
2377        "fields": False,
2378        "escaped": False,
2379        "collection_items": False,
2380        "map_keys": False,
2381        "lines": False,
2382        "null": False,
2383        "serde": False,
2384    }
2385
2386
2387class RowFormatSerdeProperty(Property):
2388    arg_types = {"this": True, "serde_properties": False}
2389
2390
2391# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2392class QueryTransform(Expression):
2393    arg_types = {
2394        "expressions": True,
2395        "command_script": True,
2396        "schema": False,
2397        "row_format_before": False,
2398        "record_writer": False,
2399        "row_format_after": False,
2400        "record_reader": False,
2401    }
2402
2403
2404class SampleProperty(Property):
2405    arg_types = {"this": True}
2406
2407
2408class SchemaCommentProperty(Property):
2409    arg_types = {"this": True}
2410
2411
2412class SerdeProperties(Property):
2413    arg_types = {"expressions": True}
2414
2415
2416class SetProperty(Property):
2417    arg_types = {"multi": True}
2418
2419
2420class SetConfigProperty(Property):
2421    arg_types = {"this": True}
2422
2423
2424class SettingsProperty(Property):
2425    arg_types = {"expressions": True}
2426
2427
2428class SortKeyProperty(Property):
2429    arg_types = {"this": True, "compound": False}
2430
2431
2432class SqlReadWriteProperty(Property):
2433    arg_types = {"this": True}
2434
2435
2436class SqlSecurityProperty(Property):
2437    arg_types = {"definer": True}
2438
2439
2440class StabilityProperty(Property):
2441    arg_types = {"this": True}
2442
2443
2444class TemporaryProperty(Property):
2445    arg_types = {}
2446
2447
2448class TransformModelProperty(Property):
2449    arg_types = {"expressions": True}
2450
2451
2452class TransientProperty(Property):
2453    arg_types = {"this": False}
2454
2455
2456class VolatileProperty(Property):
2457    arg_types = {"this": False}
2458
2459
2460class WithDataProperty(Property):
2461    arg_types = {"no": True, "statistics": False}
2462
2463
2464class WithJournalTableProperty(Property):
2465    arg_types = {"this": True}
2466
2467
2468class WithSystemVersioningProperty(Property):
2469    # this -> history table name, expression -> data consistency check
2470    arg_types = {"this": False, "expression": False}
2471
2472
2473class Properties(Expression):
2474    arg_types = {"expressions": True}
2475
2476    NAME_TO_PROPERTY = {
2477        "ALGORITHM": AlgorithmProperty,
2478        "AUTO_INCREMENT": AutoIncrementProperty,
2479        "CHARACTER SET": CharacterSetProperty,
2480        "CLUSTERED_BY": ClusteredByProperty,
2481        "COLLATE": CollateProperty,
2482        "COMMENT": SchemaCommentProperty,
2483        "DEFINER": DefinerProperty,
2484        "DISTKEY": DistKeyProperty,
2485        "DISTSTYLE": DistStyleProperty,
2486        "ENGINE": EngineProperty,
2487        "EXECUTE AS": ExecuteAsProperty,
2488        "FORMAT": FileFormatProperty,
2489        "LANGUAGE": LanguageProperty,
2490        "LOCATION": LocationProperty,
2491        "PARTITIONED_BY": PartitionedByProperty,
2492        "RETURNS": ReturnsProperty,
2493        "ROW_FORMAT": RowFormatProperty,
2494        "SORTKEY": SortKeyProperty,
2495    }
2496
2497    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2498
2499    # CREATE property locations
2500    # Form: schema specified
2501    #   create [POST_CREATE]
2502    #     table a [POST_NAME]
2503    #     (b int) [POST_SCHEMA]
2504    #     with ([POST_WITH])
2505    #     index (b) [POST_INDEX]
2506    #
2507    # Form: alias selection
2508    #   create [POST_CREATE]
2509    #     table a [POST_NAME]
2510    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2511    #     index (c) [POST_INDEX]
2512    class Location(AutoName):
2513        POST_CREATE = auto()
2514        POST_NAME = auto()
2515        POST_SCHEMA = auto()
2516        POST_WITH = auto()
2517        POST_ALIAS = auto()
2518        POST_EXPRESSION = auto()
2519        POST_INDEX = auto()
2520        UNSUPPORTED = auto()
2521
2522    @classmethod
2523    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2524        expressions = []
2525        for key, value in properties_dict.items():
2526            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2527            if property_cls:
2528                expressions.append(property_cls(this=convert(value)))
2529            else:
2530                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2531
2532        return cls(expressions=expressions)
2533
2534
2535class Qualify(Expression):
2536    pass
2537
2538
2539class InputOutputFormat(Expression):
2540    arg_types = {"input_format": False, "output_format": False}
2541
2542
2543# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2544class Return(Expression):
2545    pass
2546
2547
2548class Reference(Expression):
2549    arg_types = {"this": True, "expressions": False, "options": False}
2550
2551
2552class Tuple(Expression):
2553    arg_types = {"expressions": False}
2554
2555    def isin(
2556        self,
2557        *expressions: t.Any,
2558        query: t.Optional[ExpOrStr] = None,
2559        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2560        copy: bool = True,
2561        **opts,
2562    ) -> In:
2563        return In(
2564            this=maybe_copy(self, copy),
2565            expressions=[convert(e, copy=copy) for e in expressions],
2566            query=maybe_parse(query, copy=copy, **opts) if query else None,
2567            unnest=(
2568                Unnest(
2569                    expressions=[
2570                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2571                        for e in ensure_list(unnest)
2572                    ]
2573                )
2574                if unnest
2575                else None
2576            ),
2577        )
2578
2579
2580QUERY_MODIFIERS = {
2581    "match": False,
2582    "laterals": False,
2583    "joins": False,
2584    "connect": False,
2585    "pivots": False,
2586    "prewhere": False,
2587    "where": False,
2588    "group": False,
2589    "having": False,
2590    "qualify": False,
2591    "windows": False,
2592    "distribute": False,
2593    "sort": False,
2594    "cluster": False,
2595    "order": False,
2596    "limit": False,
2597    "offset": False,
2598    "locks": False,
2599    "sample": False,
2600    "settings": False,
2601    "format": False,
2602}
2603
2604
2605# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2606class WithTableHint(Expression):
2607    arg_types = {"expressions": True}
2608
2609
2610# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2611class IndexTableHint(Expression):
2612    arg_types = {"this": True, "expressions": False, "target": False}
2613
2614
2615# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2616class HistoricalData(Expression):
2617    arg_types = {"this": True, "kind": True, "expression": True}
2618
2619
2620class Table(Expression):
2621    arg_types = {
2622        "this": False,
2623        "alias": False,
2624        "db": False,
2625        "catalog": False,
2626        "laterals": False,
2627        "joins": False,
2628        "pivots": False,
2629        "hints": False,
2630        "system_time": False,
2631        "version": False,
2632        "format": False,
2633        "pattern": False,
2634        "ordinality": False,
2635        "when": False,
2636    }
2637
2638    @property
2639    def name(self) -> str:
2640        if isinstance(self.this, Func):
2641            return ""
2642        return self.this.name
2643
2644    @property
2645    def db(self) -> str:
2646        return self.text("db")
2647
2648    @property
2649    def catalog(self) -> str:
2650        return self.text("catalog")
2651
2652    @property
2653    def selects(self) -> t.List[Expression]:
2654        return []
2655
2656    @property
2657    def named_selects(self) -> t.List[str]:
2658        return []
2659
2660    @property
2661    def parts(self) -> t.List[Expression]:
2662        """Return the parts of a table in order catalog, db, table."""
2663        parts: t.List[Expression] = []
2664
2665        for arg in ("catalog", "db", "this"):
2666            part = self.args.get(arg)
2667
2668            if isinstance(part, Dot):
2669                parts.extend(part.flatten())
2670            elif isinstance(part, Expression):
2671                parts.append(part)
2672
2673        return parts
2674
2675    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2676        parts = self.parts
2677        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2678        alias = self.args.get("alias")
2679        if alias:
2680            col = alias_(col, alias.this, copy=copy)
2681        return col
2682
2683
2684class Union(Query):
2685    arg_types = {
2686        "with": False,
2687        "this": True,
2688        "expression": True,
2689        "distinct": False,
2690        "by_name": False,
2691        **QUERY_MODIFIERS,
2692    }
2693
2694    def select(
2695        self,
2696        *expressions: t.Optional[ExpOrStr],
2697        append: bool = True,
2698        dialect: DialectType = None,
2699        copy: bool = True,
2700        **opts,
2701    ) -> Union:
2702        this = maybe_copy(self, copy)
2703        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2704        this.expression.unnest().select(
2705            *expressions, append=append, dialect=dialect, copy=False, **opts
2706        )
2707        return this
2708
2709    @property
2710    def named_selects(self) -> t.List[str]:
2711        return self.this.unnest().named_selects
2712
2713    @property
2714    def is_star(self) -> bool:
2715        return self.this.is_star or self.expression.is_star
2716
2717    @property
2718    def selects(self) -> t.List[Expression]:
2719        return self.this.unnest().selects
2720
2721    @property
2722    def left(self) -> Expression:
2723        return self.this
2724
2725    @property
2726    def right(self) -> Expression:
2727        return self.expression
2728
2729
2730class Except(Union):
2731    pass
2732
2733
2734class Intersect(Union):
2735    pass
2736
2737
2738class Unnest(UDTF):
2739    arg_types = {
2740        "expressions": True,
2741        "alias": False,
2742        "offset": False,
2743    }
2744
2745    @property
2746    def selects(self) -> t.List[Expression]:
2747        columns = super().selects
2748        offset = self.args.get("offset")
2749        if offset:
2750            columns = columns + [to_identifier("offset") if offset is True else offset]
2751        return columns
2752
2753
2754class Update(Expression):
2755    arg_types = {
2756        "with": False,
2757        "this": False,
2758        "expressions": True,
2759        "from": False,
2760        "where": False,
2761        "returning": False,
2762        "order": False,
2763        "limit": False,
2764    }
2765
2766
2767class Values(UDTF):
2768    arg_types = {"expressions": True, "alias": False}
2769
2770
2771class Var(Expression):
2772    pass
2773
2774
2775class Version(Expression):
2776    """
2777    Time travel, iceberg, bigquery etc
2778    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2779    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2780    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2781    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2782    this is either TIMESTAMP or VERSION
2783    kind is ("AS OF", "BETWEEN")
2784    """
2785
2786    arg_types = {"this": True, "kind": True, "expression": False}
2787
2788
2789class Schema(Expression):
2790    arg_types = {"this": False, "expressions": False}
2791
2792
2793# https://dev.mysql.com/doc/refman/8.0/en/select.html
2794# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2795class Lock(Expression):
2796    arg_types = {"update": True, "expressions": False, "wait": False}
2797
2798
2799class Select(Query):
2800    arg_types = {
2801        "with": False,
2802        "kind": False,
2803        "expressions": False,
2804        "hint": False,
2805        "distinct": False,
2806        "into": False,
2807        "from": False,
2808        **QUERY_MODIFIERS,
2809    }
2810
2811    def from_(
2812        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2813    ) -> Select:
2814        """
2815        Set the FROM expression.
2816
2817        Example:
2818            >>> Select().from_("tbl").select("x").sql()
2819            'SELECT x FROM tbl'
2820
2821        Args:
2822            expression : the SQL code strings to parse.
2823                If a `From` instance is passed, this is used as-is.
2824                If another `Expression` instance is passed, it will be wrapped in a `From`.
2825            dialect: the dialect used to parse the input expression.
2826            copy: if `False`, modify this expression instance in-place.
2827            opts: other options to use to parse the input expressions.
2828
2829        Returns:
2830            The modified Select expression.
2831        """
2832        return _apply_builder(
2833            expression=expression,
2834            instance=self,
2835            arg="from",
2836            into=From,
2837            prefix="FROM",
2838            dialect=dialect,
2839            copy=copy,
2840            **opts,
2841        )
2842
2843    def group_by(
2844        self,
2845        *expressions: t.Optional[ExpOrStr],
2846        append: bool = True,
2847        dialect: DialectType = None,
2848        copy: bool = True,
2849        **opts,
2850    ) -> Select:
2851        """
2852        Set the GROUP BY expression.
2853
2854        Example:
2855            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2856            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2857
2858        Args:
2859            *expressions: the SQL code strings to parse.
2860                If a `Group` instance is passed, this is used as-is.
2861                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2862                If nothing is passed in then a group by is not applied to the expression
2863            append: if `True`, add to any existing expressions.
2864                Otherwise, this flattens all the `Group` expression into a single expression.
2865            dialect: the dialect used to parse the input expression.
2866            copy: if `False`, modify this expression instance in-place.
2867            opts: other options to use to parse the input expressions.
2868
2869        Returns:
2870            The modified Select expression.
2871        """
2872        if not expressions:
2873            return self if not copy else self.copy()
2874
2875        return _apply_child_list_builder(
2876            *expressions,
2877            instance=self,
2878            arg="group",
2879            append=append,
2880            copy=copy,
2881            prefix="GROUP BY",
2882            into=Group,
2883            dialect=dialect,
2884            **opts,
2885        )
2886
2887    def order_by(
2888        self,
2889        *expressions: t.Optional[ExpOrStr],
2890        append: bool = True,
2891        dialect: DialectType = None,
2892        copy: bool = True,
2893        **opts,
2894    ) -> Select:
2895        """
2896        Set the ORDER BY expression.
2897
2898        Example:
2899            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2900            'SELECT x FROM tbl ORDER BY x DESC'
2901
2902        Args:
2903            *expressions: the SQL code strings to parse.
2904                If a `Group` instance is passed, this is used as-is.
2905                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2906            append: if `True`, add to any existing expressions.
2907                Otherwise, this flattens all the `Order` expression into a single expression.
2908            dialect: the dialect used to parse the input expression.
2909            copy: if `False`, modify this expression instance in-place.
2910            opts: other options to use to parse the input expressions.
2911
2912        Returns:
2913            The modified Select expression.
2914        """
2915        return _apply_child_list_builder(
2916            *expressions,
2917            instance=self,
2918            arg="order",
2919            append=append,
2920            copy=copy,
2921            prefix="ORDER BY",
2922            into=Order,
2923            dialect=dialect,
2924            **opts,
2925        )
2926
2927    def sort_by(
2928        self,
2929        *expressions: t.Optional[ExpOrStr],
2930        append: bool = True,
2931        dialect: DialectType = None,
2932        copy: bool = True,
2933        **opts,
2934    ) -> Select:
2935        """
2936        Set the SORT BY expression.
2937
2938        Example:
2939            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2940            'SELECT x FROM tbl SORT BY x DESC'
2941
2942        Args:
2943            *expressions: the SQL code strings to parse.
2944                If a `Group` instance is passed, this is used as-is.
2945                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2946            append: if `True`, add to any existing expressions.
2947                Otherwise, this flattens all the `Order` expression into a single expression.
2948            dialect: the dialect used to parse the input expression.
2949            copy: if `False`, modify this expression instance in-place.
2950            opts: other options to use to parse the input expressions.
2951
2952        Returns:
2953            The modified Select expression.
2954        """
2955        return _apply_child_list_builder(
2956            *expressions,
2957            instance=self,
2958            arg="sort",
2959            append=append,
2960            copy=copy,
2961            prefix="SORT BY",
2962            into=Sort,
2963            dialect=dialect,
2964            **opts,
2965        )
2966
2967    def cluster_by(
2968        self,
2969        *expressions: t.Optional[ExpOrStr],
2970        append: bool = True,
2971        dialect: DialectType = None,
2972        copy: bool = True,
2973        **opts,
2974    ) -> Select:
2975        """
2976        Set the CLUSTER BY expression.
2977
2978        Example:
2979            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2980            'SELECT x FROM tbl CLUSTER BY x DESC'
2981
2982        Args:
2983            *expressions: the SQL code strings to parse.
2984                If a `Group` instance is passed, this is used as-is.
2985                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2986            append: if `True`, add to any existing expressions.
2987                Otherwise, this flattens all the `Order` expression into a single expression.
2988            dialect: the dialect used to parse the input expression.
2989            copy: if `False`, modify this expression instance in-place.
2990            opts: other options to use to parse the input expressions.
2991
2992        Returns:
2993            The modified Select expression.
2994        """
2995        return _apply_child_list_builder(
2996            *expressions,
2997            instance=self,
2998            arg="cluster",
2999            append=append,
3000            copy=copy,
3001            prefix="CLUSTER BY",
3002            into=Cluster,
3003            dialect=dialect,
3004            **opts,
3005        )
3006
3007    def limit(
3008        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3009    ) -> Select:
3010        return _apply_builder(
3011            expression=expression,
3012            instance=self,
3013            arg="limit",
3014            into=Limit,
3015            prefix="LIMIT",
3016            dialect=dialect,
3017            copy=copy,
3018            into_arg="expression",
3019            **opts,
3020        )
3021
3022    def offset(
3023        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3024    ) -> Select:
3025        """
3026        Set the OFFSET expression.
3027
3028        Example:
3029            >>> Select().from_("tbl").select("x").offset(10).sql()
3030            'SELECT x FROM tbl OFFSET 10'
3031
3032        Args:
3033            expression: the SQL code string to parse.
3034                This can also be an integer.
3035                If a `Offset` instance is passed, this is used as-is.
3036                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3037            dialect: the dialect used to parse the input expression.
3038            copy: if `False`, modify this expression instance in-place.
3039            opts: other options to use to parse the input expressions.
3040
3041        Returns:
3042            The modified Select expression.
3043        """
3044        return _apply_builder(
3045            expression=expression,
3046            instance=self,
3047            arg="offset",
3048            into=Offset,
3049            prefix="OFFSET",
3050            dialect=dialect,
3051            copy=copy,
3052            into_arg="expression",
3053            **opts,
3054        )
3055
3056    def select(
3057        self,
3058        *expressions: t.Optional[ExpOrStr],
3059        append: bool = True,
3060        dialect: DialectType = None,
3061        copy: bool = True,
3062        **opts,
3063    ) -> Select:
3064        return _apply_list_builder(
3065            *expressions,
3066            instance=self,
3067            arg="expressions",
3068            append=append,
3069            dialect=dialect,
3070            into=Expression,
3071            copy=copy,
3072            **opts,
3073        )
3074
3075    def lateral(
3076        self,
3077        *expressions: t.Optional[ExpOrStr],
3078        append: bool = True,
3079        dialect: DialectType = None,
3080        copy: bool = True,
3081        **opts,
3082    ) -> Select:
3083        """
3084        Append to or set the LATERAL expressions.
3085
3086        Example:
3087            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3088            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3089
3090        Args:
3091            *expressions: the SQL code strings to parse.
3092                If an `Expression` instance is passed, it will be used as-is.
3093            append: if `True`, add to any existing expressions.
3094                Otherwise, this resets the expressions.
3095            dialect: the dialect used to parse the input expressions.
3096            copy: if `False`, modify this expression instance in-place.
3097            opts: other options to use to parse the input expressions.
3098
3099        Returns:
3100            The modified Select expression.
3101        """
3102        return _apply_list_builder(
3103            *expressions,
3104            instance=self,
3105            arg="laterals",
3106            append=append,
3107            into=Lateral,
3108            prefix="LATERAL VIEW",
3109            dialect=dialect,
3110            copy=copy,
3111            **opts,
3112        )
3113
3114    def join(
3115        self,
3116        expression: ExpOrStr,
3117        on: t.Optional[ExpOrStr] = None,
3118        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3119        append: bool = True,
3120        join_type: t.Optional[str] = None,
3121        join_alias: t.Optional[Identifier | str] = None,
3122        dialect: DialectType = None,
3123        copy: bool = True,
3124        **opts,
3125    ) -> Select:
3126        """
3127        Append to or set the JOIN expressions.
3128
3129        Example:
3130            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3131            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3132
3133            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3134            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3135
3136            Use `join_type` to change the type of join:
3137
3138            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3139            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3140
3141        Args:
3142            expression: the SQL code string to parse.
3143                If an `Expression` instance is passed, it will be used as-is.
3144            on: optionally specify the join "on" criteria as a SQL string.
3145                If an `Expression` instance is passed, it will be used as-is.
3146            using: optionally specify the join "using" criteria as a SQL string.
3147                If an `Expression` instance is passed, it will be used as-is.
3148            append: if `True`, add to any existing expressions.
3149                Otherwise, this resets the expressions.
3150            join_type: if set, alter the parsed join type.
3151            join_alias: an optional alias for the joined source.
3152            dialect: the dialect used to parse the input expressions.
3153            copy: if `False`, modify this expression instance in-place.
3154            opts: other options to use to parse the input expressions.
3155
3156        Returns:
3157            Select: the modified expression.
3158        """
3159        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3160
3161        try:
3162            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3163        except ParseError:
3164            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3165
3166        join = expression if isinstance(expression, Join) else Join(this=expression)
3167
3168        if isinstance(join.this, Select):
3169            join.this.replace(join.this.subquery())
3170
3171        if join_type:
3172            method: t.Optional[Token]
3173            side: t.Optional[Token]
3174            kind: t.Optional[Token]
3175
3176            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3177
3178            if method:
3179                join.set("method", method.text)
3180            if side:
3181                join.set("side", side.text)
3182            if kind:
3183                join.set("kind", kind.text)
3184
3185        if on:
3186            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3187            join.set("on", on)
3188
3189        if using:
3190            join = _apply_list_builder(
3191                *ensure_list(using),
3192                instance=join,
3193                arg="using",
3194                append=append,
3195                copy=copy,
3196                into=Identifier,
3197                **opts,
3198            )
3199
3200        if join_alias:
3201            join.set("this", alias_(join.this, join_alias, table=True))
3202
3203        return _apply_list_builder(
3204            join,
3205            instance=self,
3206            arg="joins",
3207            append=append,
3208            copy=copy,
3209            **opts,
3210        )
3211
3212    def where(
3213        self,
3214        *expressions: t.Optional[ExpOrStr],
3215        append: bool = True,
3216        dialect: DialectType = None,
3217        copy: bool = True,
3218        **opts,
3219    ) -> Select:
3220        """
3221        Append to or set the WHERE expressions.
3222
3223        Example:
3224            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3225            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3226
3227        Args:
3228            *expressions: the SQL code strings to parse.
3229                If an `Expression` instance is passed, it will be used as-is.
3230                Multiple expressions are combined with an AND operator.
3231            append: if `True`, AND the new expressions to any existing expression.
3232                Otherwise, this resets the expression.
3233            dialect: the dialect used to parse the input expressions.
3234            copy: if `False`, modify this expression instance in-place.
3235            opts: other options to use to parse the input expressions.
3236
3237        Returns:
3238            Select: the modified expression.
3239        """
3240        return _apply_conjunction_builder(
3241            *expressions,
3242            instance=self,
3243            arg="where",
3244            append=append,
3245            into=Where,
3246            dialect=dialect,
3247            copy=copy,
3248            **opts,
3249        )
3250
3251    def having(
3252        self,
3253        *expressions: t.Optional[ExpOrStr],
3254        append: bool = True,
3255        dialect: DialectType = None,
3256        copy: bool = True,
3257        **opts,
3258    ) -> Select:
3259        """
3260        Append to or set the HAVING expressions.
3261
3262        Example:
3263            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3264            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3265
3266        Args:
3267            *expressions: the SQL code strings to parse.
3268                If an `Expression` instance is passed, it will be used as-is.
3269                Multiple expressions are combined with an AND operator.
3270            append: if `True`, AND the new expressions to any existing expression.
3271                Otherwise, this resets the expression.
3272            dialect: the dialect used to parse the input expressions.
3273            copy: if `False`, modify this expression instance in-place.
3274            opts: other options to use to parse the input expressions.
3275
3276        Returns:
3277            The modified Select expression.
3278        """
3279        return _apply_conjunction_builder(
3280            *expressions,
3281            instance=self,
3282            arg="having",
3283            append=append,
3284            into=Having,
3285            dialect=dialect,
3286            copy=copy,
3287            **opts,
3288        )
3289
3290    def window(
3291        self,
3292        *expressions: t.Optional[ExpOrStr],
3293        append: bool = True,
3294        dialect: DialectType = None,
3295        copy: bool = True,
3296        **opts,
3297    ) -> Select:
3298        return _apply_list_builder(
3299            *expressions,
3300            instance=self,
3301            arg="windows",
3302            append=append,
3303            into=Window,
3304            dialect=dialect,
3305            copy=copy,
3306            **opts,
3307        )
3308
3309    def qualify(
3310        self,
3311        *expressions: t.Optional[ExpOrStr],
3312        append: bool = True,
3313        dialect: DialectType = None,
3314        copy: bool = True,
3315        **opts,
3316    ) -> Select:
3317        return _apply_conjunction_builder(
3318            *expressions,
3319            instance=self,
3320            arg="qualify",
3321            append=append,
3322            into=Qualify,
3323            dialect=dialect,
3324            copy=copy,
3325            **opts,
3326        )
3327
3328    def distinct(
3329        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3330    ) -> Select:
3331        """
3332        Set the OFFSET expression.
3333
3334        Example:
3335            >>> Select().from_("tbl").select("x").distinct().sql()
3336            'SELECT DISTINCT x FROM tbl'
3337
3338        Args:
3339            ons: the expressions to distinct on
3340            distinct: whether the Select should be distinct
3341            copy: if `False`, modify this expression instance in-place.
3342
3343        Returns:
3344            Select: the modified expression.
3345        """
3346        instance = maybe_copy(self, copy)
3347        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3348        instance.set("distinct", Distinct(on=on) if distinct else None)
3349        return instance
3350
3351    def ctas(
3352        self,
3353        table: ExpOrStr,
3354        properties: t.Optional[t.Dict] = None,
3355        dialect: DialectType = None,
3356        copy: bool = True,
3357        **opts,
3358    ) -> Create:
3359        """
3360        Convert this expression to a CREATE TABLE AS statement.
3361
3362        Example:
3363            >>> Select().select("*").from_("tbl").ctas("x").sql()
3364            'CREATE TABLE x AS SELECT * FROM tbl'
3365
3366        Args:
3367            table: the SQL code string to parse as the table name.
3368                If another `Expression` instance is passed, it will be used as-is.
3369            properties: an optional mapping of table properties
3370            dialect: the dialect used to parse the input table.
3371            copy: if `False`, modify this expression instance in-place.
3372            opts: other options to use to parse the input table.
3373
3374        Returns:
3375            The new Create expression.
3376        """
3377        instance = maybe_copy(self, copy)
3378        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3379
3380        properties_expression = None
3381        if properties:
3382            properties_expression = Properties.from_dict(properties)
3383
3384        return Create(
3385            this=table_expression,
3386            kind="TABLE",
3387            expression=instance,
3388            properties=properties_expression,
3389        )
3390
3391    def lock(self, update: bool = True, copy: bool = True) -> Select:
3392        """
3393        Set the locking read mode for this expression.
3394
3395        Examples:
3396            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3397            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3398
3399            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3400            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3401
3402        Args:
3403            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3404            copy: if `False`, modify this expression instance in-place.
3405
3406        Returns:
3407            The modified expression.
3408        """
3409        inst = maybe_copy(self, copy)
3410        inst.set("locks", [Lock(update=update)])
3411
3412        return inst
3413
3414    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3415        """
3416        Set hints for this expression.
3417
3418        Examples:
3419            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3420            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3421
3422        Args:
3423            hints: The SQL code strings to parse as the hints.
3424                If an `Expression` instance is passed, it will be used as-is.
3425            dialect: The dialect used to parse the hints.
3426            copy: If `False`, modify this expression instance in-place.
3427
3428        Returns:
3429            The modified expression.
3430        """
3431        inst = maybe_copy(self, copy)
3432        inst.set(
3433            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3434        )
3435
3436        return inst
3437
3438    @property
3439    def named_selects(self) -> t.List[str]:
3440        return [e.output_name for e in self.expressions if e.alias_or_name]
3441
3442    @property
3443    def is_star(self) -> bool:
3444        return any(expression.is_star for expression in self.expressions)
3445
3446    @property
3447    def selects(self) -> t.List[Expression]:
3448        return self.expressions
3449
3450
3451UNWRAPPED_QUERIES = (Select, Union)
3452
3453
3454class Subquery(DerivedTable, Query):
3455    arg_types = {
3456        "this": True,
3457        "alias": False,
3458        "with": False,
3459        **QUERY_MODIFIERS,
3460    }
3461
3462    def unnest(self):
3463        """Returns the first non subquery."""
3464        expression = self
3465        while isinstance(expression, Subquery):
3466            expression = expression.this
3467        return expression
3468
3469    def unwrap(self) -> Subquery:
3470        expression = self
3471        while expression.same_parent and expression.is_wrapper:
3472            expression = t.cast(Subquery, expression.parent)
3473        return expression
3474
3475    def select(
3476        self,
3477        *expressions: t.Optional[ExpOrStr],
3478        append: bool = True,
3479        dialect: DialectType = None,
3480        copy: bool = True,
3481        **opts,
3482    ) -> Subquery:
3483        this = maybe_copy(self, copy)
3484        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3485        return this
3486
3487    @property
3488    def is_wrapper(self) -> bool:
3489        """
3490        Whether this Subquery acts as a simple wrapper around another expression.
3491
3492        SELECT * FROM (((SELECT * FROM t)))
3493                      ^
3494                      This corresponds to a "wrapper" Subquery node
3495        """
3496        return all(v is None for k, v in self.args.items() if k != "this")
3497
3498    @property
3499    def is_star(self) -> bool:
3500        return self.this.is_star
3501
3502    @property
3503    def output_name(self) -> str:
3504        return self.alias
3505
3506
3507class TableSample(Expression):
3508    arg_types = {
3509        "this": False,
3510        "expressions": False,
3511        "method": False,
3512        "bucket_numerator": False,
3513        "bucket_denominator": False,
3514        "bucket_field": False,
3515        "percent": False,
3516        "rows": False,
3517        "size": False,
3518        "seed": False,
3519    }
3520
3521
3522class Tag(Expression):
3523    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3524
3525    arg_types = {
3526        "this": False,
3527        "prefix": False,
3528        "postfix": False,
3529    }
3530
3531
3532# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3533# https://duckdb.org/docs/sql/statements/pivot
3534class Pivot(Expression):
3535    arg_types = {
3536        "this": False,
3537        "alias": False,
3538        "expressions": False,
3539        "field": False,
3540        "unpivot": False,
3541        "using": False,
3542        "group": False,
3543        "columns": False,
3544        "include_nulls": False,
3545    }
3546
3547    @property
3548    def unpivot(self) -> bool:
3549        return bool(self.args.get("unpivot"))
3550
3551
3552class Window(Condition):
3553    arg_types = {
3554        "this": True,
3555        "partition_by": False,
3556        "order": False,
3557        "spec": False,
3558        "alias": False,
3559        "over": False,
3560        "first": False,
3561    }
3562
3563
3564class WindowSpec(Expression):
3565    arg_types = {
3566        "kind": False,
3567        "start": False,
3568        "start_side": False,
3569        "end": False,
3570        "end_side": False,
3571    }
3572
3573
3574class PreWhere(Expression):
3575    pass
3576
3577
3578class Where(Expression):
3579    pass
3580
3581
3582class Star(Expression):
3583    arg_types = {"except": False, "replace": False}
3584
3585    @property
3586    def name(self) -> str:
3587        return "*"
3588
3589    @property
3590    def output_name(self) -> str:
3591        return self.name
3592
3593
3594class Parameter(Condition):
3595    arg_types = {"this": True, "expression": False}
3596
3597
3598class SessionParameter(Condition):
3599    arg_types = {"this": True, "kind": False}
3600
3601
3602class Placeholder(Condition):
3603    arg_types = {"this": False, "kind": False}
3604
3605
3606class Null(Condition):
3607    arg_types: t.Dict[str, t.Any] = {}
3608
3609    @property
3610    def name(self) -> str:
3611        return "NULL"
3612
3613
3614class Boolean(Condition):
3615    pass
3616
3617
3618class DataTypeParam(Expression):
3619    arg_types = {"this": True, "expression": False}
3620
3621
3622class DataType(Expression):
3623    arg_types = {
3624        "this": True,
3625        "expressions": False,
3626        "nested": False,
3627        "values": False,
3628        "prefix": False,
3629        "kind": False,
3630    }
3631
3632    class Type(AutoName):
3633        ARRAY = auto()
3634        AGGREGATEFUNCTION = auto()
3635        SIMPLEAGGREGATEFUNCTION = auto()
3636        BIGDECIMAL = auto()
3637        BIGINT = auto()
3638        BIGSERIAL = auto()
3639        BINARY = auto()
3640        BIT = auto()
3641        BOOLEAN = auto()
3642        BPCHAR = auto()
3643        CHAR = auto()
3644        DATE = auto()
3645        DATE32 = auto()
3646        DATEMULTIRANGE = auto()
3647        DATERANGE = auto()
3648        DATETIME = auto()
3649        DATETIME64 = auto()
3650        DECIMAL = auto()
3651        DOUBLE = auto()
3652        ENUM = auto()
3653        ENUM8 = auto()
3654        ENUM16 = auto()
3655        FIXEDSTRING = auto()
3656        FLOAT = auto()
3657        GEOGRAPHY = auto()
3658        GEOMETRY = auto()
3659        HLLSKETCH = auto()
3660        HSTORE = auto()
3661        IMAGE = auto()
3662        INET = auto()
3663        INT = auto()
3664        INT128 = auto()
3665        INT256 = auto()
3666        INT4MULTIRANGE = auto()
3667        INT4RANGE = auto()
3668        INT8MULTIRANGE = auto()
3669        INT8RANGE = auto()
3670        INTERVAL = auto()
3671        IPADDRESS = auto()
3672        IPPREFIX = auto()
3673        IPV4 = auto()
3674        IPV6 = auto()
3675        JSON = auto()
3676        JSONB = auto()
3677        LONGBLOB = auto()
3678        LONGTEXT = auto()
3679        LOWCARDINALITY = auto()
3680        MAP = auto()
3681        MEDIUMBLOB = auto()
3682        MEDIUMINT = auto()
3683        MEDIUMTEXT = auto()
3684        MONEY = auto()
3685        NCHAR = auto()
3686        NESTED = auto()
3687        NULL = auto()
3688        NULLABLE = auto()
3689        NUMMULTIRANGE = auto()
3690        NUMRANGE = auto()
3691        NVARCHAR = auto()
3692        OBJECT = auto()
3693        ROWVERSION = auto()
3694        SERIAL = auto()
3695        SET = auto()
3696        SMALLINT = auto()
3697        SMALLMONEY = auto()
3698        SMALLSERIAL = auto()
3699        STRUCT = auto()
3700        SUPER = auto()
3701        TEXT = auto()
3702        TINYBLOB = auto()
3703        TINYTEXT = auto()
3704        TIME = auto()
3705        TIMETZ = auto()
3706        TIMESTAMP = auto()
3707        TIMESTAMPLTZ = auto()
3708        TIMESTAMPTZ = auto()
3709        TIMESTAMP_S = auto()
3710        TIMESTAMP_MS = auto()
3711        TIMESTAMP_NS = auto()
3712        TINYINT = auto()
3713        TSMULTIRANGE = auto()
3714        TSRANGE = auto()
3715        TSTZMULTIRANGE = auto()
3716        TSTZRANGE = auto()
3717        UBIGINT = auto()
3718        UINT = auto()
3719        UINT128 = auto()
3720        UINT256 = auto()
3721        UMEDIUMINT = auto()
3722        UDECIMAL = auto()
3723        UNIQUEIDENTIFIER = auto()
3724        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3725        USERDEFINED = "USER-DEFINED"
3726        USMALLINT = auto()
3727        UTINYINT = auto()
3728        UUID = auto()
3729        VARBINARY = auto()
3730        VARCHAR = auto()
3731        VARIANT = auto()
3732        XML = auto()
3733        YEAR = auto()
3734
3735    TEXT_TYPES = {
3736        Type.CHAR,
3737        Type.NCHAR,
3738        Type.VARCHAR,
3739        Type.NVARCHAR,
3740        Type.TEXT,
3741    }
3742
3743    INTEGER_TYPES = {
3744        Type.INT,
3745        Type.TINYINT,
3746        Type.SMALLINT,
3747        Type.BIGINT,
3748        Type.INT128,
3749        Type.INT256,
3750        Type.BIT,
3751    }
3752
3753    FLOAT_TYPES = {
3754        Type.FLOAT,
3755        Type.DOUBLE,
3756    }
3757
3758    NUMERIC_TYPES = {
3759        *INTEGER_TYPES,
3760        *FLOAT_TYPES,
3761    }
3762
3763    TEMPORAL_TYPES = {
3764        Type.TIME,
3765        Type.TIMETZ,
3766        Type.TIMESTAMP,
3767        Type.TIMESTAMPTZ,
3768        Type.TIMESTAMPLTZ,
3769        Type.TIMESTAMP_S,
3770        Type.TIMESTAMP_MS,
3771        Type.TIMESTAMP_NS,
3772        Type.DATE,
3773        Type.DATE32,
3774        Type.DATETIME,
3775        Type.DATETIME64,
3776    }
3777
3778    @classmethod
3779    def build(
3780        cls,
3781        dtype: DATA_TYPE,
3782        dialect: DialectType = None,
3783        udt: bool = False,
3784        copy: bool = True,
3785        **kwargs,
3786    ) -> DataType:
3787        """
3788        Constructs a DataType object.
3789
3790        Args:
3791            dtype: the data type of interest.
3792            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3793            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3794                DataType, thus creating a user-defined type.
3795            copy: whether to copy the data type.
3796            kwargs: additional arguments to pass in the constructor of DataType.
3797
3798        Returns:
3799            The constructed DataType object.
3800        """
3801        from sqlglot import parse_one
3802
3803        if isinstance(dtype, str):
3804            if dtype.upper() == "UNKNOWN":
3805                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3806
3807            try:
3808                data_type_exp = parse_one(
3809                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3810                )
3811            except ParseError:
3812                if udt:
3813                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3814                raise
3815        elif isinstance(dtype, DataType.Type):
3816            data_type_exp = DataType(this=dtype)
3817        elif isinstance(dtype, DataType):
3818            return maybe_copy(dtype, copy)
3819        else:
3820            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3821
3822        return DataType(**{**data_type_exp.args, **kwargs})
3823
3824    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3825        """
3826        Checks whether this DataType matches one of the provided data types. Nested types or precision
3827        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3828
3829        Args:
3830            dtypes: the data types to compare this DataType to.
3831
3832        Returns:
3833            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3834        """
3835        for dtype in dtypes:
3836            other = DataType.build(dtype, copy=False, udt=True)
3837
3838            if (
3839                other.expressions
3840                or self.this == DataType.Type.USERDEFINED
3841                or other.this == DataType.Type.USERDEFINED
3842            ):
3843                matches = self == other
3844            else:
3845                matches = self.this == other.this
3846
3847            if matches:
3848                return True
3849        return False
3850
3851
3852DATA_TYPE = t.Union[str, DataType, DataType.Type]
3853
3854
3855# https://www.postgresql.org/docs/15/datatype-pseudo.html
3856class PseudoType(DataType):
3857    arg_types = {"this": True}
3858
3859
3860# https://www.postgresql.org/docs/15/datatype-oid.html
3861class ObjectIdentifier(DataType):
3862    arg_types = {"this": True}
3863
3864
3865# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3866class SubqueryPredicate(Predicate):
3867    pass
3868
3869
3870class All(SubqueryPredicate):
3871    pass
3872
3873
3874class Any(SubqueryPredicate):
3875    pass
3876
3877
3878class Exists(SubqueryPredicate):
3879    pass
3880
3881
3882# Commands to interact with the databases or engines. For most of the command
3883# expressions we parse whatever comes after the command's name as a string.
3884class Command(Expression):
3885    arg_types = {"this": True, "expression": False}
3886
3887
3888class Transaction(Expression):
3889    arg_types = {"this": False, "modes": False, "mark": False}
3890
3891
3892class Commit(Expression):
3893    arg_types = {"chain": False, "this": False, "durability": False}
3894
3895
3896class Rollback(Expression):
3897    arg_types = {"savepoint": False, "this": False}
3898
3899
3900class AlterTable(Expression):
3901    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3902
3903
3904class AddConstraint(Expression):
3905    arg_types = {"expressions": True}
3906
3907
3908class DropPartition(Expression):
3909    arg_types = {"expressions": True, "exists": False}
3910
3911
3912# Binary expressions like (ADD a b)
3913class Binary(Condition):
3914    arg_types = {"this": True, "expression": True}
3915
3916    @property
3917    def left(self) -> Expression:
3918        return self.this
3919
3920    @property
3921    def right(self) -> Expression:
3922        return self.expression
3923
3924
3925class Add(Binary):
3926    pass
3927
3928
3929class Connector(Binary):
3930    pass
3931
3932
3933class And(Connector):
3934    pass
3935
3936
3937class Or(Connector):
3938    pass
3939
3940
3941class BitwiseAnd(Binary):
3942    pass
3943
3944
3945class BitwiseLeftShift(Binary):
3946    pass
3947
3948
3949class BitwiseOr(Binary):
3950    pass
3951
3952
3953class BitwiseRightShift(Binary):
3954    pass
3955
3956
3957class BitwiseXor(Binary):
3958    pass
3959
3960
3961class Div(Binary):
3962    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3963
3964
3965class Overlaps(Binary):
3966    pass
3967
3968
3969class Dot(Binary):
3970    @property
3971    def name(self) -> str:
3972        return self.expression.name
3973
3974    @property
3975    def output_name(self) -> str:
3976        return self.name
3977
3978    @classmethod
3979    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3980        """Build a Dot object with a sequence of expressions."""
3981        if len(expressions) < 2:
3982            raise ValueError("Dot requires >= 2 expressions.")
3983
3984        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3985
3986    @property
3987    def parts(self) -> t.List[Expression]:
3988        """Return the parts of a table / column in order catalog, db, table."""
3989        this, *parts = self.flatten()
3990
3991        parts.reverse()
3992
3993        for arg in ("this", "table", "db", "catalog"):
3994            part = this.args.get(arg)
3995
3996            if isinstance(part, Expression):
3997                parts.append(part)
3998
3999        parts.reverse()
4000        return parts
4001
4002
4003class DPipe(Binary):
4004    arg_types = {"this": True, "expression": True, "safe": False}
4005
4006
4007class EQ(Binary, Predicate):
4008    pass
4009
4010
4011class NullSafeEQ(Binary, Predicate):
4012    pass
4013
4014
4015class NullSafeNEQ(Binary, Predicate):
4016    pass
4017
4018
4019# Represents e.g. := in DuckDB which is mostly used for setting parameters
4020class PropertyEQ(Binary):
4021    pass
4022
4023
4024class Distance(Binary):
4025    pass
4026
4027
4028class Escape(Binary):
4029    pass
4030
4031
4032class Glob(Binary, Predicate):
4033    pass
4034
4035
4036class GT(Binary, Predicate):
4037    pass
4038
4039
4040class GTE(Binary, Predicate):
4041    pass
4042
4043
4044class ILike(Binary, Predicate):
4045    pass
4046
4047
4048class ILikeAny(Binary, Predicate):
4049    pass
4050
4051
4052class IntDiv(Binary):
4053    pass
4054
4055
4056class Is(Binary, Predicate):
4057    pass
4058
4059
4060class Kwarg(Binary):
4061    """Kwarg in special functions like func(kwarg => y)."""
4062
4063
4064class Like(Binary, Predicate):
4065    pass
4066
4067
4068class LikeAny(Binary, Predicate):
4069    pass
4070
4071
4072class LT(Binary, Predicate):
4073    pass
4074
4075
4076class LTE(Binary, Predicate):
4077    pass
4078
4079
4080class Mod(Binary):
4081    pass
4082
4083
4084class Mul(Binary):
4085    pass
4086
4087
4088class NEQ(Binary, Predicate):
4089    pass
4090
4091
4092# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4093class Operator(Binary):
4094    arg_types = {"this": True, "operator": True, "expression": True}
4095
4096
4097class SimilarTo(Binary, Predicate):
4098    pass
4099
4100
4101class Slice(Binary):
4102    arg_types = {"this": False, "expression": False}
4103
4104
4105class Sub(Binary):
4106    pass
4107
4108
4109# Unary Expressions
4110# (NOT a)
4111class Unary(Condition):
4112    pass
4113
4114
4115class BitwiseNot(Unary):
4116    pass
4117
4118
4119class Not(Unary):
4120    pass
4121
4122
4123class Paren(Unary):
4124    arg_types = {"this": True, "with": False}
4125
4126    @property
4127    def output_name(self) -> str:
4128        return self.this.name
4129
4130
4131class Neg(Unary):
4132    pass
4133
4134
4135class Alias(Expression):
4136    arg_types = {"this": True, "alias": False}
4137
4138    @property
4139    def output_name(self) -> str:
4140        return self.alias
4141
4142
4143# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4144# other dialects require identifiers. This enables us to transpile between them easily.
4145class PivotAlias(Alias):
4146    pass
4147
4148
4149class Aliases(Expression):
4150    arg_types = {"this": True, "expressions": True}
4151
4152    @property
4153    def aliases(self):
4154        return self.expressions
4155
4156
4157# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4158class AtIndex(Expression):
4159    arg_types = {"this": True, "expression": True}
4160
4161
4162class AtTimeZone(Expression):
4163    arg_types = {"this": True, "zone": True}
4164
4165
4166class FromTimeZone(Expression):
4167    arg_types = {"this": True, "zone": True}
4168
4169
4170class Between(Predicate):
4171    arg_types = {"this": True, "low": True, "high": True}
4172
4173
4174class Bracket(Condition):
4175    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4176    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4177
4178    @property
4179    def output_name(self) -> str:
4180        if len(self.expressions) == 1:
4181            return self.expressions[0].output_name
4182
4183        return super().output_name
4184
4185
4186class Distinct(Expression):
4187    arg_types = {"expressions": False, "on": False}
4188
4189
4190class In(Predicate):
4191    arg_types = {
4192        "this": True,
4193        "expressions": False,
4194        "query": False,
4195        "unnest": False,
4196        "field": False,
4197        "is_global": False,
4198    }
4199
4200
4201# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4202class ForIn(Expression):
4203    arg_types = {"this": True, "expression": True}
4204
4205
4206class TimeUnit(Expression):
4207    """Automatically converts unit arg into a var."""
4208
4209    arg_types = {"unit": False}
4210
4211    UNABBREVIATED_UNIT_NAME = {
4212        "D": "DAY",
4213        "H": "HOUR",
4214        "M": "MINUTE",
4215        "MS": "MILLISECOND",
4216        "NS": "NANOSECOND",
4217        "Q": "QUARTER",
4218        "S": "SECOND",
4219        "US": "MICROSECOND",
4220        "W": "WEEK",
4221        "Y": "YEAR",
4222    }
4223
4224    VAR_LIKE = (Column, Literal, Var)
4225
4226    def __init__(self, **args):
4227        unit = args.get("unit")
4228        if isinstance(unit, self.VAR_LIKE):
4229            args["unit"] = Var(
4230                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4231            )
4232        elif isinstance(unit, Week):
4233            unit.set("this", Var(this=unit.this.name.upper()))
4234
4235        super().__init__(**args)
4236
4237    @property
4238    def unit(self) -> t.Optional[Var]:
4239        return self.args.get("unit")
4240
4241
4242class IntervalOp(TimeUnit):
4243    arg_types = {"unit": True, "expression": True}
4244
4245    def interval(self):
4246        return Interval(
4247            this=self.expression.copy(),
4248            unit=self.unit.copy(),
4249        )
4250
4251
4252# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4253# https://trino.io/docs/current/language/types.html#interval-day-to-second
4254# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4255class IntervalSpan(DataType):
4256    arg_types = {"this": True, "expression": True}
4257
4258
4259class Interval(TimeUnit):
4260    arg_types = {"this": False, "unit": False}
4261
4262
4263class IgnoreNulls(Expression):
4264    pass
4265
4266
4267class RespectNulls(Expression):
4268    pass
4269
4270
4271# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4272class HavingMax(Expression):
4273    arg_types = {"this": True, "expression": True, "max": True}
4274
4275
4276# Functions
4277class Func(Condition):
4278    """
4279    The base class for all function expressions.
4280
4281    Attributes:
4282        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4283            treated as a variable length argument and the argument's value will be stored as a list.
4284        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4285            function expression. These values are used to map this node to a name during parsing as
4286            well as to provide the function's name during SQL string generation. By default the SQL
4287            name is set to the expression's class name transformed to snake case.
4288    """
4289
4290    is_var_len_args = False
4291
4292    @classmethod
4293    def from_arg_list(cls, args):
4294        if cls.is_var_len_args:
4295            all_arg_keys = list(cls.arg_types)
4296            # If this function supports variable length argument treat the last argument as such.
4297            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4298            num_non_var = len(non_var_len_arg_keys)
4299
4300            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4301            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4302        else:
4303            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4304
4305        return cls(**args_dict)
4306
4307    @classmethod
4308    def sql_names(cls):
4309        if cls is Func:
4310            raise NotImplementedError(
4311                "SQL name is only supported by concrete function implementations"
4312            )
4313        if "_sql_names" not in cls.__dict__:
4314            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4315        return cls._sql_names
4316
4317    @classmethod
4318    def sql_name(cls):
4319        return cls.sql_names()[0]
4320
4321    @classmethod
4322    def default_parser_mappings(cls):
4323        return {name: cls.from_arg_list for name in cls.sql_names()}
4324
4325
4326class AggFunc(Func):
4327    pass
4328
4329
4330class ParameterizedAgg(AggFunc):
4331    arg_types = {"this": True, "expressions": True, "params": True}
4332
4333
4334class Abs(Func):
4335    pass
4336
4337
4338class ArgMax(AggFunc):
4339    arg_types = {"this": True, "expression": True, "count": False}
4340    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4341
4342
4343class ArgMin(AggFunc):
4344    arg_types = {"this": True, "expression": True, "count": False}
4345    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4346
4347
4348class ApproxTopK(AggFunc):
4349    arg_types = {"this": True, "expression": False, "counters": False}
4350
4351
4352class Flatten(Func):
4353    pass
4354
4355
4356# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4357class Transform(Func):
4358    arg_types = {"this": True, "expression": True}
4359
4360
4361class Anonymous(Func):
4362    arg_types = {"this": True, "expressions": False}
4363    is_var_len_args = True
4364
4365
4366class AnonymousAggFunc(AggFunc):
4367    arg_types = {"this": True, "expressions": False}
4368    is_var_len_args = True
4369
4370
4371# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4372class CombinedAggFunc(AnonymousAggFunc):
4373    arg_types = {"this": True, "expressions": False, "parts": True}
4374
4375
4376class CombinedParameterizedAgg(ParameterizedAgg):
4377    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4378
4379
4380# https://docs.snowflake.com/en/sql-reference/functions/hll
4381# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4382class Hll(AggFunc):
4383    arg_types = {"this": True, "expressions": False}
4384    is_var_len_args = True
4385
4386
4387class ApproxDistinct(AggFunc):
4388    arg_types = {"this": True, "accuracy": False}
4389    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4390
4391
4392class Array(Func):
4393    arg_types = {"expressions": False}
4394    is_var_len_args = True
4395
4396
4397# https://docs.snowflake.com/en/sql-reference/functions/to_array
4398class ToArray(Func):
4399    pass
4400
4401
4402# https://docs.snowflake.com/en/sql-reference/functions/to_char
4403# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4404class ToChar(Func):
4405    arg_types = {"this": True, "format": False, "nlsparam": False}
4406
4407
4408class GenerateSeries(Func):
4409    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4410
4411
4412class ArrayAgg(AggFunc):
4413    pass
4414
4415
4416class ArrayUniqueAgg(AggFunc):
4417    pass
4418
4419
4420class ArrayAll(Func):
4421    arg_types = {"this": True, "expression": True}
4422
4423
4424# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4425class ArrayAny(Func):
4426    arg_types = {"this": True, "expression": True}
4427
4428
4429class ArrayConcat(Func):
4430    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4431    arg_types = {"this": True, "expressions": False}
4432    is_var_len_args = True
4433
4434
4435class ArrayContains(Binary, Func):
4436    pass
4437
4438
4439class ArrayContained(Binary):
4440    pass
4441
4442
4443class ArrayFilter(Func):
4444    arg_types = {"this": True, "expression": True}
4445    _sql_names = ["FILTER", "ARRAY_FILTER"]
4446
4447
4448class ArrayJoin(Func):
4449    arg_types = {"this": True, "expression": True, "null": False}
4450
4451
4452class ArrayOverlaps(Binary, Func):
4453    pass
4454
4455
4456class ArraySize(Func):
4457    arg_types = {"this": True, "expression": False}
4458    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4459
4460
4461class ArraySort(Func):
4462    arg_types = {"this": True, "expression": False}
4463
4464
4465class ArraySum(Func):
4466    arg_types = {"this": True, "expression": False}
4467
4468
4469class ArrayUnionAgg(AggFunc):
4470    pass
4471
4472
4473class Avg(AggFunc):
4474    pass
4475
4476
4477class AnyValue(AggFunc):
4478    pass
4479
4480
4481class Lag(AggFunc):
4482    arg_types = {"this": True, "offset": False, "default": False}
4483
4484
4485class Lead(AggFunc):
4486    arg_types = {"this": True, "offset": False, "default": False}
4487
4488
4489# some dialects have a distinction between first and first_value, usually first is an aggregate func
4490# and first_value is a window func
4491class First(AggFunc):
4492    pass
4493
4494
4495class Last(AggFunc):
4496    pass
4497
4498
4499class FirstValue(AggFunc):
4500    pass
4501
4502
4503class LastValue(AggFunc):
4504    pass
4505
4506
4507class NthValue(AggFunc):
4508    arg_types = {"this": True, "offset": True}
4509
4510
4511class Case(Func):
4512    arg_types = {"this": False, "ifs": True, "default": False}
4513
4514    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4515        instance = maybe_copy(self, copy)
4516        instance.append(
4517            "ifs",
4518            If(
4519                this=maybe_parse(condition, copy=copy, **opts),
4520                true=maybe_parse(then, copy=copy, **opts),
4521            ),
4522        )
4523        return instance
4524
4525    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4526        instance = maybe_copy(self, copy)
4527        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4528        return instance
4529
4530
4531class Cast(Func):
4532    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4533
4534    @property
4535    def name(self) -> str:
4536        return self.this.name
4537
4538    @property
4539    def to(self) -> DataType:
4540        return self.args["to"]
4541
4542    @property
4543    def output_name(self) -> str:
4544        return self.name
4545
4546    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4547        """
4548        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4549        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4550        array<int> != array<float>.
4551
4552        Args:
4553            dtypes: the data types to compare this Cast's DataType to.
4554
4555        Returns:
4556            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4557        """
4558        return self.to.is_type(*dtypes)
4559
4560
4561class TryCast(Cast):
4562    pass
4563
4564
4565class CastToStrType(Func):
4566    arg_types = {"this": True, "to": True}
4567
4568
4569class Collate(Binary, Func):
4570    pass
4571
4572
4573class Ceil(Func):
4574    arg_types = {"this": True, "decimals": False}
4575    _sql_names = ["CEIL", "CEILING"]
4576
4577
4578class Coalesce(Func):
4579    arg_types = {"this": True, "expressions": False}
4580    is_var_len_args = True
4581    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4582
4583
4584class Chr(Func):
4585    arg_types = {"this": True, "charset": False, "expressions": False}
4586    is_var_len_args = True
4587    _sql_names = ["CHR", "CHAR"]
4588
4589
4590class Concat(Func):
4591    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4592    is_var_len_args = True
4593
4594
4595class ConcatWs(Concat):
4596    _sql_names = ["CONCAT_WS"]
4597
4598
4599class Count(AggFunc):
4600    arg_types = {"this": False, "expressions": False}
4601    is_var_len_args = True
4602
4603
4604class CountIf(AggFunc):
4605    _sql_names = ["COUNT_IF", "COUNTIF"]
4606
4607
4608# cube root
4609class Cbrt(Func):
4610    pass
4611
4612
4613class CurrentDate(Func):
4614    arg_types = {"this": False}
4615
4616
4617class CurrentDatetime(Func):
4618    arg_types = {"this": False}
4619
4620
4621class CurrentTime(Func):
4622    arg_types = {"this": False}
4623
4624
4625class CurrentTimestamp(Func):
4626    arg_types = {"this": False, "transaction": False}
4627
4628
4629class CurrentUser(Func):
4630    arg_types = {"this": False}
4631
4632
4633class DateAdd(Func, IntervalOp):
4634    arg_types = {"this": True, "expression": True, "unit": False}
4635
4636
4637class DateSub(Func, IntervalOp):
4638    arg_types = {"this": True, "expression": True, "unit": False}
4639
4640
4641class DateDiff(Func, TimeUnit):
4642    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4643    arg_types = {"this": True, "expression": True, "unit": False}
4644
4645
4646class DateTrunc(Func):
4647    arg_types = {"unit": True, "this": True, "zone": False}
4648
4649    def __init__(self, **args):
4650        unit = args.get("unit")
4651        if isinstance(unit, TimeUnit.VAR_LIKE):
4652            args["unit"] = Literal.string(
4653                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4654            )
4655        elif isinstance(unit, Week):
4656            unit.set("this", Literal.string(unit.this.name.upper()))
4657
4658        super().__init__(**args)
4659
4660    @property
4661    def unit(self) -> Expression:
4662        return self.args["unit"]
4663
4664
4665class DatetimeAdd(Func, IntervalOp):
4666    arg_types = {"this": True, "expression": True, "unit": False}
4667
4668
4669class DatetimeSub(Func, IntervalOp):
4670    arg_types = {"this": True, "expression": True, "unit": False}
4671
4672
4673class DatetimeDiff(Func, TimeUnit):
4674    arg_types = {"this": True, "expression": True, "unit": False}
4675
4676
4677class DatetimeTrunc(Func, TimeUnit):
4678    arg_types = {"this": True, "unit": True, "zone": False}
4679
4680
4681class DayOfWeek(Func):
4682    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4683
4684
4685class DayOfMonth(Func):
4686    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4687
4688
4689class DayOfYear(Func):
4690    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4691
4692
4693class ToDays(Func):
4694    pass
4695
4696
4697class WeekOfYear(Func):
4698    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4699
4700
4701class MonthsBetween(Func):
4702    arg_types = {"this": True, "expression": True, "roundoff": False}
4703
4704
4705class LastDay(Func, TimeUnit):
4706    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4707    arg_types = {"this": True, "unit": False}
4708
4709
4710class Extract(Func):
4711    arg_types = {"this": True, "expression": True}
4712
4713
4714class Timestamp(Func):
4715    arg_types = {"this": False, "expression": False, "with_tz": False}
4716
4717
4718class TimestampAdd(Func, TimeUnit):
4719    arg_types = {"this": True, "expression": True, "unit": False}
4720
4721
4722class TimestampSub(Func, TimeUnit):
4723    arg_types = {"this": True, "expression": True, "unit": False}
4724
4725
4726class TimestampDiff(Func, TimeUnit):
4727    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4728    arg_types = {"this": True, "expression": True, "unit": False}
4729
4730
4731class TimestampTrunc(Func, TimeUnit):
4732    arg_types = {"this": True, "unit": True, "zone": False}
4733
4734
4735class TimeAdd(Func, TimeUnit):
4736    arg_types = {"this": True, "expression": True, "unit": False}
4737
4738
4739class TimeSub(Func, TimeUnit):
4740    arg_types = {"this": True, "expression": True, "unit": False}
4741
4742
4743class TimeDiff(Func, TimeUnit):
4744    arg_types = {"this": True, "expression": True, "unit": False}
4745
4746
4747class TimeTrunc(Func, TimeUnit):
4748    arg_types = {"this": True, "unit": True, "zone": False}
4749
4750
4751class DateFromParts(Func):
4752    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4753    arg_types = {"year": True, "month": True, "day": True}
4754
4755
4756class TimeFromParts(Func):
4757    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4758    arg_types = {
4759        "hour": True,
4760        "min": True,
4761        "sec": True,
4762        "nano": False,
4763        "fractions": False,
4764        "precision": False,
4765    }
4766
4767
4768class DateStrToDate(Func):
4769    pass
4770
4771
4772class DateToDateStr(Func):
4773    pass
4774
4775
4776class DateToDi(Func):
4777    pass
4778
4779
4780# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4781class Date(Func):
4782    arg_types = {"this": False, "zone": False, "expressions": False}
4783    is_var_len_args = True
4784
4785
4786class Day(Func):
4787    pass
4788
4789
4790class Decode(Func):
4791    arg_types = {"this": True, "charset": True, "replace": False}
4792
4793
4794class DiToDate(Func):
4795    pass
4796
4797
4798class Encode(Func):
4799    arg_types = {"this": True, "charset": True}
4800
4801
4802class Exp(Func):
4803    pass
4804
4805
4806# https://docs.snowflake.com/en/sql-reference/functions/flatten
4807class Explode(Func):
4808    arg_types = {"this": True, "expressions": False}
4809    is_var_len_args = True
4810
4811
4812class ExplodeOuter(Explode):
4813    pass
4814
4815
4816class Posexplode(Explode):
4817    pass
4818
4819
4820class PosexplodeOuter(Posexplode, ExplodeOuter):
4821    pass
4822
4823
4824class Floor(Func):
4825    arg_types = {"this": True, "decimals": False}
4826
4827
4828class FromBase64(Func):
4829    pass
4830
4831
4832class ToBase64(Func):
4833    pass
4834
4835
4836class Greatest(Func):
4837    arg_types = {"this": True, "expressions": False}
4838    is_var_len_args = True
4839
4840
4841class GroupConcat(AggFunc):
4842    arg_types = {"this": True, "separator": False}
4843
4844
4845class Hex(Func):
4846    pass
4847
4848
4849class Xor(Connector, Func):
4850    arg_types = {"this": False, "expression": False, "expressions": False}
4851
4852
4853class If(Func):
4854    arg_types = {"this": True, "true": True, "false": False}
4855    _sql_names = ["IF", "IIF"]
4856
4857
4858class Nullif(Func):
4859    arg_types = {"this": True, "expression": True}
4860
4861
4862class Initcap(Func):
4863    arg_types = {"this": True, "expression": False}
4864
4865
4866class IsNan(Func):
4867    _sql_names = ["IS_NAN", "ISNAN"]
4868
4869
4870class IsInf(Func):
4871    _sql_names = ["IS_INF", "ISINF"]
4872
4873
4874class JSONPath(Expression):
4875    arg_types = {"expressions": True}
4876
4877    @property
4878    def output_name(self) -> str:
4879        last_segment = self.expressions[-1].this
4880        return last_segment if isinstance(last_segment, str) else ""
4881
4882
4883class JSONPathPart(Expression):
4884    arg_types = {}
4885
4886
4887class JSONPathFilter(JSONPathPart):
4888    arg_types = {"this": True}
4889
4890
4891class JSONPathKey(JSONPathPart):
4892    arg_types = {"this": True}
4893
4894
4895class JSONPathRecursive(JSONPathPart):
4896    arg_types = {"this": False}
4897
4898
4899class JSONPathRoot(JSONPathPart):
4900    pass
4901
4902
4903class JSONPathScript(JSONPathPart):
4904    arg_types = {"this": True}
4905
4906
4907class JSONPathSlice(JSONPathPart):
4908    arg_types = {"start": False, "end": False, "step": False}
4909
4910
4911class JSONPathSelector(JSONPathPart):
4912    arg_types = {"this": True}
4913
4914
4915class JSONPathSubscript(JSONPathPart):
4916    arg_types = {"this": True}
4917
4918
4919class JSONPathUnion(JSONPathPart):
4920    arg_types = {"expressions": True}
4921
4922
4923class JSONPathWildcard(JSONPathPart):
4924    pass
4925
4926
4927class FormatJson(Expression):
4928    pass
4929
4930
4931class JSONKeyValue(Expression):
4932    arg_types = {"this": True, "expression": True}
4933
4934
4935class JSONObject(Func):
4936    arg_types = {
4937        "expressions": False,
4938        "null_handling": False,
4939        "unique_keys": False,
4940        "return_type": False,
4941        "encoding": False,
4942    }
4943
4944
4945class JSONObjectAgg(AggFunc):
4946    arg_types = {
4947        "expressions": False,
4948        "null_handling": False,
4949        "unique_keys": False,
4950        "return_type": False,
4951        "encoding": False,
4952    }
4953
4954
4955# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4956class JSONArray(Func):
4957    arg_types = {
4958        "expressions": True,
4959        "null_handling": False,
4960        "return_type": False,
4961        "strict": False,
4962    }
4963
4964
4965# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4966class JSONArrayAgg(Func):
4967    arg_types = {
4968        "this": True,
4969        "order": False,
4970        "null_handling": False,
4971        "return_type": False,
4972        "strict": False,
4973    }
4974
4975
4976# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4977# Note: parsing of JSON column definitions is currently incomplete.
4978class JSONColumnDef(Expression):
4979    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4980
4981
4982class JSONSchema(Expression):
4983    arg_types = {"expressions": True}
4984
4985
4986# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4987class JSONTable(Func):
4988    arg_types = {
4989        "this": True,
4990        "schema": True,
4991        "path": False,
4992        "error_handling": False,
4993        "empty_handling": False,
4994    }
4995
4996
4997class OpenJSONColumnDef(Expression):
4998    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4999
5000
5001class OpenJSON(Func):
5002    arg_types = {"this": True, "path": False, "expressions": False}
5003
5004
5005class JSONBContains(Binary):
5006    _sql_names = ["JSONB_CONTAINS"]
5007
5008
5009class JSONExtract(Binary, Func):
5010    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5011    _sql_names = ["JSON_EXTRACT"]
5012    is_var_len_args = True
5013
5014    @property
5015    def output_name(self) -> str:
5016        return self.expression.output_name if not self.expressions else ""
5017
5018
5019class JSONExtractScalar(Binary, Func):
5020    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5021    _sql_names = ["JSON_EXTRACT_SCALAR"]
5022    is_var_len_args = True
5023
5024    @property
5025    def output_name(self) -> str:
5026        return self.expression.output_name
5027
5028
5029class JSONBExtract(Binary, Func):
5030    _sql_names = ["JSONB_EXTRACT"]
5031
5032
5033class JSONBExtractScalar(Binary, Func):
5034    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5035
5036
5037class JSONFormat(Func):
5038    arg_types = {"this": False, "options": False}
5039    _sql_names = ["JSON_FORMAT"]
5040
5041
5042# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5043class JSONArrayContains(Binary, Predicate, Func):
5044    _sql_names = ["JSON_ARRAY_CONTAINS"]
5045
5046
5047class ParseJSON(Func):
5048    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5049    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5050    arg_types = {"this": True, "expressions": False}
5051    is_var_len_args = True
5052
5053
5054class Least(Func):
5055    arg_types = {"this": True, "expressions": False}
5056    is_var_len_args = True
5057
5058
5059class Left(Func):
5060    arg_types = {"this": True, "expression": True}
5061
5062
5063class Right(Func):
5064    arg_types = {"this": True, "expression": True}
5065
5066
5067class Length(Func):
5068    _sql_names = ["LENGTH", "LEN"]
5069
5070
5071class Levenshtein(Func):
5072    arg_types = {
5073        "this": True,
5074        "expression": False,
5075        "ins_cost": False,
5076        "del_cost": False,
5077        "sub_cost": False,
5078    }
5079
5080
5081class Ln(Func):
5082    pass
5083
5084
5085class Log(Func):
5086    arg_types = {"this": True, "expression": False}
5087
5088
5089class Log2(Func):
5090    pass
5091
5092
5093class Log10(Func):
5094    pass
5095
5096
5097class LogicalOr(AggFunc):
5098    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5099
5100
5101class LogicalAnd(AggFunc):
5102    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5103
5104
5105class Lower(Func):
5106    _sql_names = ["LOWER", "LCASE"]
5107
5108
5109class Map(Func):
5110    arg_types = {"keys": False, "values": False}
5111
5112    @property
5113    def keys(self) -> t.List[Expression]:
5114        keys = self.args.get("keys")
5115        return keys.expressions if keys else []
5116
5117    @property
5118    def values(self) -> t.List[Expression]:
5119        values = self.args.get("values")
5120        return values.expressions if values else []
5121
5122
5123class MapFromEntries(Func):
5124    pass
5125
5126
5127class StarMap(Func):
5128    pass
5129
5130
5131class VarMap(Func):
5132    arg_types = {"keys": True, "values": True}
5133    is_var_len_args = True
5134
5135    @property
5136    def keys(self) -> t.List[Expression]:
5137        return self.args["keys"].expressions
5138
5139    @property
5140    def values(self) -> t.List[Expression]:
5141        return self.args["values"].expressions
5142
5143
5144# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5145class MatchAgainst(Func):
5146    arg_types = {"this": True, "expressions": True, "modifier": False}
5147
5148
5149class Max(AggFunc):
5150    arg_types = {"this": True, "expressions": False}
5151    is_var_len_args = True
5152
5153
5154class MD5(Func):
5155    _sql_names = ["MD5"]
5156
5157
5158# Represents the variant of the MD5 function that returns a binary value
5159class MD5Digest(Func):
5160    _sql_names = ["MD5_DIGEST"]
5161
5162
5163class Min(AggFunc):
5164    arg_types = {"this": True, "expressions": False}
5165    is_var_len_args = True
5166
5167
5168class Month(Func):
5169    pass
5170
5171
5172class AddMonths(Func):
5173    arg_types = {"this": True, "expression": True}
5174
5175
5176class Nvl2(Func):
5177    arg_types = {"this": True, "true": True, "false": False}
5178
5179
5180# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5181class Predict(Func):
5182    arg_types = {"this": True, "expression": True, "params_struct": False}
5183
5184
5185class Pow(Binary, Func):
5186    _sql_names = ["POWER", "POW"]
5187
5188
5189class PercentileCont(AggFunc):
5190    arg_types = {"this": True, "expression": False}
5191
5192
5193class PercentileDisc(AggFunc):
5194    arg_types = {"this": True, "expression": False}
5195
5196
5197class Quantile(AggFunc):
5198    arg_types = {"this": True, "quantile": True}
5199
5200
5201class ApproxQuantile(Quantile):
5202    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5203
5204
5205class Rand(Func):
5206    _sql_names = ["RAND", "RANDOM"]
5207    arg_types = {"this": False}
5208
5209
5210class Randn(Func):
5211    arg_types = {"this": False}
5212
5213
5214class RangeN(Func):
5215    arg_types = {"this": True, "expressions": True, "each": False}
5216
5217
5218class ReadCSV(Func):
5219    _sql_names = ["READ_CSV"]
5220    is_var_len_args = True
5221    arg_types = {"this": True, "expressions": False}
5222
5223
5224class Reduce(Func):
5225    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5226
5227
5228class RegexpExtract(Func):
5229    arg_types = {
5230        "this": True,
5231        "expression": True,
5232        "position": False,
5233        "occurrence": False,
5234        "parameters": False,
5235        "group": False,
5236    }
5237
5238
5239class RegexpReplace(Func):
5240    arg_types = {
5241        "this": True,
5242        "expression": True,
5243        "replacement": False,
5244        "position": False,
5245        "occurrence": False,
5246        "parameters": False,
5247        "modifiers": False,
5248    }
5249
5250
5251class RegexpLike(Binary, Func):
5252    arg_types = {"this": True, "expression": True, "flag": False}
5253
5254
5255class RegexpILike(Binary, Func):
5256    arg_types = {"this": True, "expression": True, "flag": False}
5257
5258
5259# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5260# limit is the number of times a pattern is applied
5261class RegexpSplit(Func):
5262    arg_types = {"this": True, "expression": True, "limit": False}
5263
5264
5265class Repeat(Func):
5266    arg_types = {"this": True, "times": True}
5267
5268
5269# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5270# tsql third argument function == trunctaion if not 0
5271class Round(Func):
5272    arg_types = {"this": True, "decimals": False, "truncate": False}
5273
5274
5275class RowNumber(Func):
5276    arg_types: t.Dict[str, t.Any] = {}
5277
5278
5279class SafeDivide(Func):
5280    arg_types = {"this": True, "expression": True}
5281
5282
5283class SHA(Func):
5284    _sql_names = ["SHA", "SHA1"]
5285
5286
5287class SHA2(Func):
5288    _sql_names = ["SHA2"]
5289    arg_types = {"this": True, "length": False}
5290
5291
5292class Sign(Func):
5293    _sql_names = ["SIGN", "SIGNUM"]
5294
5295
5296class SortArray(Func):
5297    arg_types = {"this": True, "asc": False}
5298
5299
5300class Split(Func):
5301    arg_types = {"this": True, "expression": True, "limit": False}
5302
5303
5304# Start may be omitted in the case of postgres
5305# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5306class Substring(Func):
5307    arg_types = {"this": True, "start": False, "length": False}
5308
5309
5310class StandardHash(Func):
5311    arg_types = {"this": True, "expression": False}
5312
5313
5314class StartsWith(Func):
5315    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5316    arg_types = {"this": True, "expression": True}
5317
5318
5319class StrPosition(Func):
5320    arg_types = {
5321        "this": True,
5322        "substr": True,
5323        "position": False,
5324        "instance": False,
5325    }
5326
5327
5328class StrToDate(Func):
5329    arg_types = {"this": True, "format": True}
5330
5331
5332class StrToTime(Func):
5333    arg_types = {"this": True, "format": True, "zone": False}
5334
5335
5336# Spark allows unix_timestamp()
5337# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5338class StrToUnix(Func):
5339    arg_types = {"this": False, "format": False}
5340
5341
5342# https://prestodb.io/docs/current/functions/string.html
5343# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5344class StrToMap(Func):
5345    arg_types = {
5346        "this": True,
5347        "pair_delim": False,
5348        "key_value_delim": False,
5349        "duplicate_resolution_callback": False,
5350    }
5351
5352
5353class NumberToStr(Func):
5354    arg_types = {"this": True, "format": True, "culture": False}
5355
5356
5357class FromBase(Func):
5358    arg_types = {"this": True, "expression": True}
5359
5360
5361class Struct(Func):
5362    arg_types = {"expressions": False}
5363    is_var_len_args = True
5364
5365
5366class StructExtract(Func):
5367    arg_types = {"this": True, "expression": True}
5368
5369
5370# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5371# https://docs.snowflake.com/en/sql-reference/functions/insert
5372class Stuff(Func):
5373    _sql_names = ["STUFF", "INSERT"]
5374    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5375
5376
5377class Sum(AggFunc):
5378    pass
5379
5380
5381class Sqrt(Func):
5382    pass
5383
5384
5385class Stddev(AggFunc):
5386    pass
5387
5388
5389class StddevPop(AggFunc):
5390    pass
5391
5392
5393class StddevSamp(AggFunc):
5394    pass
5395
5396
5397class TimeToStr(Func):
5398    arg_types = {"this": True, "format": True, "culture": False}
5399
5400
5401class TimeToTimeStr(Func):
5402    pass
5403
5404
5405class TimeToUnix(Func):
5406    pass
5407
5408
5409class TimeStrToDate(Func):
5410    pass
5411
5412
5413class TimeStrToTime(Func):
5414    pass
5415
5416
5417class TimeStrToUnix(Func):
5418    pass
5419
5420
5421class Trim(Func):
5422    arg_types = {
5423        "this": True,
5424        "expression": False,
5425        "position": False,
5426        "collation": False,
5427    }
5428
5429
5430class TsOrDsAdd(Func, TimeUnit):
5431    # return_type is used to correctly cast the arguments of this expression when transpiling it
5432    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5433
5434    @property
5435    def return_type(self) -> DataType:
5436        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5437
5438
5439class TsOrDsDiff(Func, TimeUnit):
5440    arg_types = {"this": True, "expression": True, "unit": False}
5441
5442
5443class TsOrDsToDateStr(Func):
5444    pass
5445
5446
5447class TsOrDsToDate(Func):
5448    arg_types = {"this": True, "format": False}
5449
5450
5451class TsOrDsToTime(Func):
5452    pass
5453
5454
5455class TsOrDiToDi(Func):
5456    pass
5457
5458
5459class Unhex(Func):
5460    pass
5461
5462
5463# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5464class UnixDate(Func):
5465    pass
5466
5467
5468class UnixToStr(Func):
5469    arg_types = {"this": True, "format": False}
5470
5471
5472# https://prestodb.io/docs/current/functions/datetime.html
5473# presto has weird zone/hours/minutes
5474class UnixToTime(Func):
5475    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5476
5477    SECONDS = Literal.number(0)
5478    DECIS = Literal.number(1)
5479    CENTIS = Literal.number(2)
5480    MILLIS = Literal.number(3)
5481    DECIMILLIS = Literal.number(4)
5482    CENTIMILLIS = Literal.number(5)
5483    MICROS = Literal.number(6)
5484    DECIMICROS = Literal.number(7)
5485    CENTIMICROS = Literal.number(8)
5486    NANOS = Literal.number(9)
5487
5488
5489class UnixToTimeStr(Func):
5490    pass
5491
5492
5493class TimestampFromParts(Func):
5494    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5495    arg_types = {
5496        "year": True,
5497        "month": True,
5498        "day": True,
5499        "hour": True,
5500        "min": True,
5501        "sec": True,
5502        "nano": False,
5503        "zone": False,
5504        "milli": False,
5505    }
5506
5507
5508class Upper(Func):
5509    _sql_names = ["UPPER", "UCASE"]
5510
5511
5512class Variance(AggFunc):
5513    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5514
5515
5516class VariancePop(AggFunc):
5517    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5518
5519
5520class Week(Func):
5521    arg_types = {"this": True, "mode": False}
5522
5523
5524class XMLTable(Func):
5525    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5526
5527
5528class Year(Func):
5529    pass
5530
5531
5532class Use(Expression):
5533    arg_types = {"this": True, "kind": False}
5534
5535
5536class Merge(Expression):
5537    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5538
5539
5540class When(Func):
5541    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5542
5543
5544# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5545# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5546class NextValueFor(Func):
5547    arg_types = {"this": True, "order": False}
5548
5549
5550def _norm_arg(arg):
5551    return arg.lower() if type(arg) is str else arg
5552
5553
5554ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5555FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5556
5557JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5558
5559
5560# Helpers
5561@t.overload
5562def maybe_parse(
5563    sql_or_expression: ExpOrStr,
5564    *,
5565    into: t.Type[E],
5566    dialect: DialectType = None,
5567    prefix: t.Optional[str] = None,
5568    copy: bool = False,
5569    **opts,
5570) -> E:
5571    ...
5572
5573
5574@t.overload
5575def maybe_parse(
5576    sql_or_expression: str | E,
5577    *,
5578    into: t.Optional[IntoType] = None,
5579    dialect: DialectType = None,
5580    prefix: t.Optional[str] = None,
5581    copy: bool = False,
5582    **opts,
5583) -> E:
5584    ...
5585
5586
5587def maybe_parse(
5588    sql_or_expression: ExpOrStr,
5589    *,
5590    into: t.Optional[IntoType] = None,
5591    dialect: DialectType = None,
5592    prefix: t.Optional[str] = None,
5593    copy: bool = False,
5594    **opts,
5595) -> Expression:
5596    """Gracefully handle a possible string or expression.
5597
5598    Example:
5599        >>> maybe_parse("1")
5600        Literal(this=1, is_string=False)
5601        >>> maybe_parse(to_identifier("x"))
5602        Identifier(this=x, quoted=False)
5603
5604    Args:
5605        sql_or_expression: the SQL code string or an expression
5606        into: the SQLGlot Expression to parse into
5607        dialect: the dialect used to parse the input expressions (in the case that an
5608            input expression is a SQL string).
5609        prefix: a string to prefix the sql with before it gets parsed
5610            (automatically includes a space)
5611        copy: whether to copy the expression.
5612        **opts: other options to use to parse the input expressions (again, in the case
5613            that an input expression is a SQL string).
5614
5615    Returns:
5616        Expression: the parsed or given expression.
5617    """
5618    if isinstance(sql_or_expression, Expression):
5619        if copy:
5620            return sql_or_expression.copy()
5621        return sql_or_expression
5622
5623    if sql_or_expression is None:
5624        raise ParseError("SQL cannot be None")
5625
5626    import sqlglot
5627
5628    sql = str(sql_or_expression)
5629    if prefix:
5630        sql = f"{prefix} {sql}"
5631
5632    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5633
5634
5635@t.overload
5636def maybe_copy(instance: None, copy: bool = True) -> None:
5637    ...
5638
5639
5640@t.overload
5641def maybe_copy(instance: E, copy: bool = True) -> E:
5642    ...
5643
5644
5645def maybe_copy(instance, copy=True):
5646    return instance.copy() if copy and instance else instance
5647
5648
5649def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5650    """Generate a textual representation of an Expression tree"""
5651    indent = "\n" + ("  " * (level + 1))
5652    delim = f",{indent}"
5653
5654    if isinstance(node, Expression):
5655        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5656
5657        if (node.type or verbose) and not isinstance(node, DataType):
5658            args["_type"] = node.type
5659        if node.comments or verbose:
5660            args["_comments"] = node.comments
5661
5662        if verbose:
5663            args["_id"] = id(node)
5664
5665        # Inline leaves for a more compact representation
5666        if node.is_leaf():
5667            indent = ""
5668            delim = ", "
5669
5670        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5671        return f"{node.__class__.__name__}({indent}{items})"
5672
5673    if isinstance(node, list):
5674        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5675        items = f"{indent}{items}" if items else ""
5676        return f"[{items}]"
5677
5678    # Indent multiline strings to match the current level
5679    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5680
5681
5682def _is_wrong_expression(expression, into):
5683    return isinstance(expression, Expression) and not isinstance(expression, into)
5684
5685
5686def _apply_builder(
5687    expression,
5688    instance,
5689    arg,
5690    copy=True,
5691    prefix=None,
5692    into=None,
5693    dialect=None,
5694    into_arg="this",
5695    **opts,
5696):
5697    if _is_wrong_expression(expression, into):
5698        expression = into(**{into_arg: expression})
5699    instance = maybe_copy(instance, copy)
5700    expression = maybe_parse(
5701        sql_or_expression=expression,
5702        prefix=prefix,
5703        into=into,
5704        dialect=dialect,
5705        **opts,
5706    )
5707    instance.set(arg, expression)
5708    return instance
5709
5710
5711def _apply_child_list_builder(
5712    *expressions,
5713    instance,
5714    arg,
5715    append=True,
5716    copy=True,
5717    prefix=None,
5718    into=None,
5719    dialect=None,
5720    properties=None,
5721    **opts,
5722):
5723    instance = maybe_copy(instance, copy)
5724    parsed = []
5725    for expression in expressions:
5726        if expression is not None:
5727            if _is_wrong_expression(expression, into):
5728                expression = into(expressions=[expression])
5729
5730            expression = maybe_parse(
5731                expression,
5732                into=into,
5733                dialect=dialect,
5734                prefix=prefix,
5735                **opts,
5736            )
5737            parsed.extend(expression.expressions)
5738
5739    existing = instance.args.get(arg)
5740    if append and existing:
5741        parsed = existing.expressions + parsed
5742
5743    child = into(expressions=parsed)
5744    for k, v in (properties or {}).items():
5745        child.set(k, v)
5746    instance.set(arg, child)
5747
5748    return instance
5749
5750
5751def _apply_list_builder(
5752    *expressions,
5753    instance,
5754    arg,
5755    append=True,
5756    copy=True,
5757    prefix=None,
5758    into=None,
5759    dialect=None,
5760    **opts,
5761):
5762    inst = maybe_copy(instance, copy)
5763
5764    expressions = [
5765        maybe_parse(
5766            sql_or_expression=expression,
5767            into=into,
5768            prefix=prefix,
5769            dialect=dialect,
5770            **opts,
5771        )
5772        for expression in expressions
5773        if expression is not None
5774    ]
5775
5776    existing_expressions = inst.args.get(arg)
5777    if append and existing_expressions:
5778        expressions = existing_expressions + expressions
5779
5780    inst.set(arg, expressions)
5781    return inst
5782
5783
5784def _apply_conjunction_builder(
5785    *expressions,
5786    instance,
5787    arg,
5788    into=None,
5789    append=True,
5790    copy=True,
5791    dialect=None,
5792    **opts,
5793):
5794    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5795    if not expressions:
5796        return instance
5797
5798    inst = maybe_copy(instance, copy)
5799
5800    existing = inst.args.get(arg)
5801    if append and existing is not None:
5802        expressions = [existing.this if into else existing] + list(expressions)
5803
5804    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5805
5806    inst.set(arg, into(this=node) if into else node)
5807    return inst
5808
5809
5810def _apply_cte_builder(
5811    instance: E,
5812    alias: ExpOrStr,
5813    as_: ExpOrStr,
5814    recursive: t.Optional[bool] = None,
5815    append: bool = True,
5816    dialect: DialectType = None,
5817    copy: bool = True,
5818    **opts,
5819) -> E:
5820    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5821    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5822    cte = CTE(this=as_expression, alias=alias_expression)
5823    return _apply_child_list_builder(
5824        cte,
5825        instance=instance,
5826        arg="with",
5827        append=append,
5828        copy=copy,
5829        into=With,
5830        properties={"recursive": recursive or False},
5831    )
5832
5833
5834def _combine(
5835    expressions: t.Sequence[t.Optional[ExpOrStr]],
5836    operator: t.Type[Connector],
5837    dialect: DialectType = None,
5838    copy: bool = True,
5839    **opts,
5840) -> Expression:
5841    conditions = [
5842        condition(expression, dialect=dialect, copy=copy, **opts)
5843        for expression in expressions
5844        if expression is not None
5845    ]
5846
5847    this, *rest = conditions
5848    if rest:
5849        this = _wrap(this, Connector)
5850    for expression in rest:
5851        this = operator(this=this, expression=_wrap(expression, Connector))
5852
5853    return this
5854
5855
5856def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5857    return Paren(this=expression) if isinstance(expression, kind) else expression
5858
5859
5860def union(
5861    left: ExpOrStr,
5862    right: ExpOrStr,
5863    distinct: bool = True,
5864    dialect: DialectType = None,
5865    copy: bool = True,
5866    **opts,
5867) -> Union:
5868    """
5869    Initializes a syntax tree from one UNION expression.
5870
5871    Example:
5872        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5873        'SELECT * FROM foo UNION SELECT * FROM bla'
5874
5875    Args:
5876        left: the SQL code string corresponding to the left-hand side.
5877            If an `Expression` instance is passed, it will be used as-is.
5878        right: the SQL code string corresponding to the right-hand side.
5879            If an `Expression` instance is passed, it will be used as-is.
5880        distinct: set the DISTINCT flag if and only if this is true.
5881        dialect: the dialect used to parse the input expression.
5882        copy: whether to copy the expression.
5883        opts: other options to use to parse the input expressions.
5884
5885    Returns:
5886        The new Union instance.
5887    """
5888    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5889    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5890
5891    return Union(this=left, expression=right, distinct=distinct)
5892
5893
5894def intersect(
5895    left: ExpOrStr,
5896    right: ExpOrStr,
5897    distinct: bool = True,
5898    dialect: DialectType = None,
5899    copy: bool = True,
5900    **opts,
5901) -> Intersect:
5902    """
5903    Initializes a syntax tree from one INTERSECT expression.
5904
5905    Example:
5906        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5907        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5908
5909    Args:
5910        left: the SQL code string corresponding to the left-hand side.
5911            If an `Expression` instance is passed, it will be used as-is.
5912        right: the SQL code string corresponding to the right-hand side.
5913            If an `Expression` instance is passed, it will be used as-is.
5914        distinct: set the DISTINCT flag if and only if this is true.
5915        dialect: the dialect used to parse the input expression.
5916        copy: whether to copy the expression.
5917        opts: other options to use to parse the input expressions.
5918
5919    Returns:
5920        The new Intersect instance.
5921    """
5922    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5923    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5924
5925    return Intersect(this=left, expression=right, distinct=distinct)
5926
5927
5928def except_(
5929    left: ExpOrStr,
5930    right: ExpOrStr,
5931    distinct: bool = True,
5932    dialect: DialectType = None,
5933    copy: bool = True,
5934    **opts,
5935) -> Except:
5936    """
5937    Initializes a syntax tree from one EXCEPT expression.
5938
5939    Example:
5940        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5941        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5942
5943    Args:
5944        left: the SQL code string corresponding to the left-hand side.
5945            If an `Expression` instance is passed, it will be used as-is.
5946        right: the SQL code string corresponding to the right-hand side.
5947            If an `Expression` instance is passed, it will be used as-is.
5948        distinct: set the DISTINCT flag if and only if this is true.
5949        dialect: the dialect used to parse the input expression.
5950        copy: whether to copy the expression.
5951        opts: other options to use to parse the input expressions.
5952
5953    Returns:
5954        The new Except instance.
5955    """
5956    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5957    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5958
5959    return Except(this=left, expression=right, distinct=distinct)
5960
5961
5962def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5963    """
5964    Initializes a syntax tree from one or multiple SELECT expressions.
5965
5966    Example:
5967        >>> select("col1", "col2").from_("tbl").sql()
5968        'SELECT col1, col2 FROM tbl'
5969
5970    Args:
5971        *expressions: the SQL code string to parse as the expressions of a
5972            SELECT statement. If an Expression instance is passed, this is used as-is.
5973        dialect: the dialect used to parse the input expressions (in the case that an
5974            input expression is a SQL string).
5975        **opts: other options to use to parse the input expressions (again, in the case
5976            that an input expression is a SQL string).
5977
5978    Returns:
5979        Select: the syntax tree for the SELECT statement.
5980    """
5981    return Select().select(*expressions, dialect=dialect, **opts)
5982
5983
5984def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5985    """
5986    Initializes a syntax tree from a FROM expression.
5987
5988    Example:
5989        >>> from_("tbl").select("col1", "col2").sql()
5990        'SELECT col1, col2 FROM tbl'
5991
5992    Args:
5993        *expression: the SQL code string to parse as the FROM expressions of a
5994            SELECT statement. If an Expression instance is passed, this is used as-is.
5995        dialect: the dialect used to parse the input expression (in the case that the
5996            input expression is a SQL string).
5997        **opts: other options to use to parse the input expressions (again, in the case
5998            that the input expression is a SQL string).
5999
6000    Returns:
6001        Select: the syntax tree for the SELECT statement.
6002    """
6003    return Select().from_(expression, dialect=dialect, **opts)
6004
6005
6006def update(
6007    table: str | Table,
6008    properties: dict,
6009    where: t.Optional[ExpOrStr] = None,
6010    from_: t.Optional[ExpOrStr] = None,
6011    dialect: DialectType = None,
6012    **opts,
6013) -> Update:
6014    """
6015    Creates an update statement.
6016
6017    Example:
6018        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6019        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6020
6021    Args:
6022        *properties: dictionary of properties to set which are
6023            auto converted to sql objects eg None -> NULL
6024        where: sql conditional parsed into a WHERE statement
6025        from_: sql statement parsed into a FROM statement
6026        dialect: the dialect used to parse the input expressions.
6027        **opts: other options to use to parse the input expressions.
6028
6029    Returns:
6030        Update: the syntax tree for the UPDATE statement.
6031    """
6032    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6033    update_expr.set(
6034        "expressions",
6035        [
6036            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6037            for k, v in properties.items()
6038        ],
6039    )
6040    if from_:
6041        update_expr.set(
6042            "from",
6043            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6044        )
6045    if isinstance(where, Condition):
6046        where = Where(this=where)
6047    if where:
6048        update_expr.set(
6049            "where",
6050            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6051        )
6052    return update_expr
6053
6054
6055def delete(
6056    table: ExpOrStr,
6057    where: t.Optional[ExpOrStr] = None,
6058    returning: t.Optional[ExpOrStr] = None,
6059    dialect: DialectType = None,
6060    **opts,
6061) -> Delete:
6062    """
6063    Builds a delete statement.
6064
6065    Example:
6066        >>> delete("my_table", where="id > 1").sql()
6067        'DELETE FROM my_table WHERE id > 1'
6068
6069    Args:
6070        where: sql conditional parsed into a WHERE statement
6071        returning: sql conditional parsed into a RETURNING statement
6072        dialect: the dialect used to parse the input expressions.
6073        **opts: other options to use to parse the input expressions.
6074
6075    Returns:
6076        Delete: the syntax tree for the DELETE statement.
6077    """
6078    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6079    if where:
6080        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6081    if returning:
6082        delete_expr = t.cast(
6083            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6084        )
6085    return delete_expr
6086
6087
6088def insert(
6089    expression: ExpOrStr,
6090    into: ExpOrStr,
6091    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6092    overwrite: t.Optional[bool] = None,
6093    returning: t.Optional[ExpOrStr] = None,
6094    dialect: DialectType = None,
6095    copy: bool = True,
6096    **opts,
6097) -> Insert:
6098    """
6099    Builds an INSERT statement.
6100
6101    Example:
6102        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6103        'INSERT INTO tbl VALUES (1, 2, 3)'
6104
6105    Args:
6106        expression: the sql string or expression of the INSERT statement
6107        into: the tbl to insert data to.
6108        columns: optionally the table's column names.
6109        overwrite: whether to INSERT OVERWRITE or not.
6110        returning: sql conditional parsed into a RETURNING statement
6111        dialect: the dialect used to parse the input expressions.
6112        copy: whether to copy the expression.
6113        **opts: other options to use to parse the input expressions.
6114
6115    Returns:
6116        Insert: the syntax tree for the INSERT statement.
6117    """
6118    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6119    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6120
6121    if columns:
6122        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6123
6124    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6125
6126    if returning:
6127        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6128
6129    return insert
6130
6131
6132def condition(
6133    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6134) -> Condition:
6135    """
6136    Initialize a logical condition expression.
6137
6138    Example:
6139        >>> condition("x=1").sql()
6140        'x = 1'
6141
6142        This is helpful for composing larger logical syntax trees:
6143        >>> where = condition("x=1")
6144        >>> where = where.and_("y=1")
6145        >>> Select().from_("tbl").select("*").where(where).sql()
6146        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6147
6148    Args:
6149        *expression: the SQL code string to parse.
6150            If an Expression instance is passed, this is used as-is.
6151        dialect: the dialect used to parse the input expression (in the case that the
6152            input expression is a SQL string).
6153        copy: Whether to copy `expression` (only applies to expressions).
6154        **opts: other options to use to parse the input expressions (again, in the case
6155            that the input expression is a SQL string).
6156
6157    Returns:
6158        The new Condition instance
6159    """
6160    return maybe_parse(
6161        expression,
6162        into=Condition,
6163        dialect=dialect,
6164        copy=copy,
6165        **opts,
6166    )
6167
6168
6169def and_(
6170    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6171) -> Condition:
6172    """
6173    Combine multiple conditions with an AND logical operator.
6174
6175    Example:
6176        >>> and_("x=1", and_("y=1", "z=1")).sql()
6177        'x = 1 AND (y = 1 AND z = 1)'
6178
6179    Args:
6180        *expressions: the SQL code strings to parse.
6181            If an Expression instance is passed, this is used as-is.
6182        dialect: the dialect used to parse the input expression.
6183        copy: whether to copy `expressions` (only applies to Expressions).
6184        **opts: other options to use to parse the input expressions.
6185
6186    Returns:
6187        And: the new condition
6188    """
6189    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6190
6191
6192def or_(
6193    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6194) -> Condition:
6195    """
6196    Combine multiple conditions with an OR logical operator.
6197
6198    Example:
6199        >>> or_("x=1", or_("y=1", "z=1")).sql()
6200        'x = 1 OR (y = 1 OR z = 1)'
6201
6202    Args:
6203        *expressions: the SQL code strings to parse.
6204            If an Expression instance is passed, this is used as-is.
6205        dialect: the dialect used to parse the input expression.
6206        copy: whether to copy `expressions` (only applies to Expressions).
6207        **opts: other options to use to parse the input expressions.
6208
6209    Returns:
6210        Or: the new condition
6211    """
6212    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6213
6214
6215def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6216    """
6217    Wrap a condition with a NOT operator.
6218
6219    Example:
6220        >>> not_("this_suit='black'").sql()
6221        "NOT this_suit = 'black'"
6222
6223    Args:
6224        expression: the SQL code string to parse.
6225            If an Expression instance is passed, this is used as-is.
6226        dialect: the dialect used to parse the input expression.
6227        copy: whether to copy the expression or not.
6228        **opts: other options to use to parse the input expressions.
6229
6230    Returns:
6231        The new condition.
6232    """
6233    this = condition(
6234        expression,
6235        dialect=dialect,
6236        copy=copy,
6237        **opts,
6238    )
6239    return Not(this=_wrap(this, Connector))
6240
6241
6242def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6243    """
6244    Wrap an expression in parentheses.
6245
6246    Example:
6247        >>> paren("5 + 3").sql()
6248        '(5 + 3)'
6249
6250    Args:
6251        expression: the SQL code string to parse.
6252            If an Expression instance is passed, this is used as-is.
6253        copy: whether to copy the expression or not.
6254
6255    Returns:
6256        The wrapped expression.
6257    """
6258    return Paren(this=maybe_parse(expression, copy=copy))
6259
6260
6261SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6262
6263
6264@t.overload
6265def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
6266    ...
6267
6268
6269@t.overload
6270def to_identifier(
6271    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6272) -> Identifier:
6273    ...
6274
6275
6276def to_identifier(name, quoted=None, copy=True):
6277    """Builds an identifier.
6278
6279    Args:
6280        name: The name to turn into an identifier.
6281        quoted: Whether to force quote the identifier.
6282        copy: Whether to copy name if it's an Identifier.
6283
6284    Returns:
6285        The identifier ast node.
6286    """
6287
6288    if name is None:
6289        return None
6290
6291    if isinstance(name, Identifier):
6292        identifier = maybe_copy(name, copy)
6293    elif isinstance(name, str):
6294        identifier = Identifier(
6295            this=name,
6296            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6297        )
6298    else:
6299        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6300    return identifier
6301
6302
6303def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6304    """
6305    Parses a given string into an identifier.
6306
6307    Args:
6308        name: The name to parse into an identifier.
6309        dialect: The dialect to parse against.
6310
6311    Returns:
6312        The identifier ast node.
6313    """
6314    try:
6315        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6316    except ParseError:
6317        expression = to_identifier(name)
6318
6319    return expression
6320
6321
6322INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6323
6324
6325def to_interval(interval: str | Literal) -> Interval:
6326    """Builds an interval expression from a string like '1 day' or '5 months'."""
6327    if isinstance(interval, Literal):
6328        if not interval.is_string:
6329            raise ValueError("Invalid interval string.")
6330
6331        interval = interval.this
6332
6333    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6334
6335    if not interval_parts:
6336        raise ValueError("Invalid interval string.")
6337
6338    return Interval(
6339        this=Literal.string(interval_parts.group(1)),
6340        unit=Var(this=interval_parts.group(2).upper()),
6341    )
6342
6343
6344@t.overload
6345def to_table(sql_path: str | Table, **kwargs) -> Table:
6346    ...
6347
6348
6349@t.overload
6350def to_table(sql_path: None, **kwargs) -> None:
6351    ...
6352
6353
6354def to_table(
6355    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6356) -> t.Optional[Table]:
6357    """
6358    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6359    If a table is passed in then that table is returned.
6360
6361    Args:
6362        sql_path: a `[catalog].[schema].[table]` string.
6363        dialect: the source dialect according to which the table name will be parsed.
6364        copy: Whether to copy a table if it is passed in.
6365        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6366
6367    Returns:
6368        A table expression.
6369    """
6370    if sql_path is None or isinstance(sql_path, Table):
6371        return maybe_copy(sql_path, copy=copy)
6372    if not isinstance(sql_path, str):
6373        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6374
6375    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6376    if table:
6377        for k, v in kwargs.items():
6378            table.set(k, v)
6379
6380    return table
6381
6382
6383def to_column(sql_path: str | Column, **kwargs) -> Column:
6384    """
6385    Create a column from a `[table].[column]` sql path. Schema is optional.
6386
6387    If a column is passed in then that column is returned.
6388
6389    Args:
6390        sql_path: `[table].[column]` string
6391    Returns:
6392        Table: A column expression
6393    """
6394    if sql_path is None or isinstance(sql_path, Column):
6395        return sql_path
6396    if not isinstance(sql_path, str):
6397        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6398    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6399
6400
6401def alias_(
6402    expression: ExpOrStr,
6403    alias: t.Optional[str | Identifier],
6404    table: bool | t.Sequence[str | Identifier] = False,
6405    quoted: t.Optional[bool] = None,
6406    dialect: DialectType = None,
6407    copy: bool = True,
6408    **opts,
6409):
6410    """Create an Alias expression.
6411
6412    Example:
6413        >>> alias_('foo', 'bar').sql()
6414        'foo AS bar'
6415
6416        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6417        '(SELECT 1, 2) AS bar(a, b)'
6418
6419    Args:
6420        expression: the SQL code strings to parse.
6421            If an Expression instance is passed, this is used as-is.
6422        alias: the alias name to use. If the name has
6423            special characters it is quoted.
6424        table: Whether to create a table alias, can also be a list of columns.
6425        quoted: whether to quote the alias
6426        dialect: the dialect used to parse the input expression.
6427        copy: Whether to copy the expression.
6428        **opts: other options to use to parse the input expressions.
6429
6430    Returns:
6431        Alias: the aliased expression
6432    """
6433    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6434    alias = to_identifier(alias, quoted=quoted)
6435
6436    if table:
6437        table_alias = TableAlias(this=alias)
6438        exp.set("alias", table_alias)
6439
6440        if not isinstance(table, bool):
6441            for column in table:
6442                table_alias.append("columns", to_identifier(column, quoted=quoted))
6443
6444        return exp
6445
6446    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6447    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6448    # for the complete Window expression.
6449    #
6450    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6451
6452    if "alias" in exp.arg_types and not isinstance(exp, Window):
6453        exp.set("alias", alias)
6454        return exp
6455    return Alias(this=exp, alias=alias)
6456
6457
6458def subquery(
6459    expression: ExpOrStr,
6460    alias: t.Optional[Identifier | str] = None,
6461    dialect: DialectType = None,
6462    **opts,
6463) -> Select:
6464    """
6465    Build a subquery expression.
6466
6467    Example:
6468        >>> subquery('select x from tbl', 'bar').select('x').sql()
6469        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6470
6471    Args:
6472        expression: the SQL code strings to parse.
6473            If an Expression instance is passed, this is used as-is.
6474        alias: the alias name to use.
6475        dialect: the dialect used to parse the input expression.
6476        **opts: other options to use to parse the input expressions.
6477
6478    Returns:
6479        A new Select instance with the subquery expression included.
6480    """
6481
6482    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6483    return Select().from_(expression, dialect=dialect, **opts)
6484
6485
6486@t.overload
6487def column(
6488    col: str | Identifier,
6489    table: t.Optional[str | Identifier] = None,
6490    db: t.Optional[str | Identifier] = None,
6491    catalog: t.Optional[str | Identifier] = None,
6492    *,
6493    fields: t.Collection[t.Union[str, Identifier]],
6494    quoted: t.Optional[bool] = None,
6495    copy: bool = True,
6496) -> Dot:
6497    pass
6498
6499
6500@t.overload
6501def column(
6502    col: str | Identifier,
6503    table: t.Optional[str | Identifier] = None,
6504    db: t.Optional[str | Identifier] = None,
6505    catalog: t.Optional[str | Identifier] = None,
6506    *,
6507    fields: Lit[None] = None,
6508    quoted: t.Optional[bool] = None,
6509    copy: bool = True,
6510) -> Column:
6511    pass
6512
6513
6514def column(
6515    col,
6516    table=None,
6517    db=None,
6518    catalog=None,
6519    *,
6520    fields=None,
6521    quoted=None,
6522    copy=True,
6523):
6524    """
6525    Build a Column.
6526
6527    Args:
6528        col: Column name.
6529        table: Table name.
6530        db: Database name.
6531        catalog: Catalog name.
6532        fields: Additional fields using dots.
6533        quoted: Whether to force quotes on the column's identifiers.
6534        copy: Whether to copy identifiers if passed in.
6535
6536    Returns:
6537        The new Column instance.
6538    """
6539    this = Column(
6540        this=to_identifier(col, quoted=quoted, copy=copy),
6541        table=to_identifier(table, quoted=quoted, copy=copy),
6542        db=to_identifier(db, quoted=quoted, copy=copy),
6543        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6544    )
6545
6546    if fields:
6547        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6548    return this
6549
6550
6551def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6552    """Cast an expression to a data type.
6553
6554    Example:
6555        >>> cast('x + 1', 'int').sql()
6556        'CAST(x + 1 AS INT)'
6557
6558    Args:
6559        expression: The expression to cast.
6560        to: The datatype to cast to.
6561        copy: Whether to copy the supplied expressions.
6562
6563    Returns:
6564        The new Cast instance.
6565    """
6566    expression = maybe_parse(expression, copy=copy, **opts)
6567    data_type = DataType.build(to, copy=copy, **opts)
6568    expression = Cast(this=expression, to=data_type)
6569    expression.type = data_type
6570    return expression
6571
6572
6573def table_(
6574    table: Identifier | str,
6575    db: t.Optional[Identifier | str] = None,
6576    catalog: t.Optional[Identifier | str] = None,
6577    quoted: t.Optional[bool] = None,
6578    alias: t.Optional[Identifier | str] = None,
6579) -> Table:
6580    """Build a Table.
6581
6582    Args:
6583        table: Table name.
6584        db: Database name.
6585        catalog: Catalog name.
6586        quote: Whether to force quotes on the table's identifiers.
6587        alias: Table's alias.
6588
6589    Returns:
6590        The new Table instance.
6591    """
6592    return Table(
6593        this=to_identifier(table, quoted=quoted) if table else None,
6594        db=to_identifier(db, quoted=quoted) if db else None,
6595        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6596        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6597    )
6598
6599
6600def values(
6601    values: t.Iterable[t.Tuple[t.Any, ...]],
6602    alias: t.Optional[str] = None,
6603    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6604) -> Values:
6605    """Build VALUES statement.
6606
6607    Example:
6608        >>> values([(1, '2')]).sql()
6609        "VALUES (1, '2')"
6610
6611    Args:
6612        values: values statements that will be converted to SQL
6613        alias: optional alias
6614        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6615         If either are provided then an alias is also required.
6616
6617    Returns:
6618        Values: the Values expression object
6619    """
6620    if columns and not alias:
6621        raise ValueError("Alias is required when providing columns")
6622
6623    return Values(
6624        expressions=[convert(tup) for tup in values],
6625        alias=(
6626            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6627            if columns
6628            else (TableAlias(this=to_identifier(alias)) if alias else None)
6629        ),
6630    )
6631
6632
6633def var(name: t.Optional[ExpOrStr]) -> Var:
6634    """Build a SQL variable.
6635
6636    Example:
6637        >>> repr(var('x'))
6638        'Var(this=x)'
6639
6640        >>> repr(var(column('x', table='y')))
6641        'Var(this=x)'
6642
6643    Args:
6644        name: The name of the var or an expression who's name will become the var.
6645
6646    Returns:
6647        The new variable node.
6648    """
6649    if not name:
6650        raise ValueError("Cannot convert empty name into var.")
6651
6652    if isinstance(name, Expression):
6653        name = name.name
6654    return Var(this=name)
6655
6656
6657def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6658    """Build ALTER TABLE... RENAME... expression
6659
6660    Args:
6661        old_name: The old name of the table
6662        new_name: The new name of the table
6663
6664    Returns:
6665        Alter table expression
6666    """
6667    old_table = to_table(old_name)
6668    new_table = to_table(new_name)
6669    return AlterTable(
6670        this=old_table,
6671        actions=[
6672            RenameTable(this=new_table),
6673        ],
6674    )
6675
6676
6677def rename_column(
6678    table_name: str | Table,
6679    old_column_name: str | Column,
6680    new_column_name: str | Column,
6681    exists: t.Optional[bool] = None,
6682) -> AlterTable:
6683    """Build ALTER TABLE... RENAME COLUMN... expression
6684
6685    Args:
6686        table_name: Name of the table
6687        old_column: The old name of the column
6688        new_column: The new name of the column
6689        exists: Whether to add the `IF EXISTS` clause
6690
6691    Returns:
6692        Alter table expression
6693    """
6694    table = to_table(table_name)
6695    old_column = to_column(old_column_name)
6696    new_column = to_column(new_column_name)
6697    return AlterTable(
6698        this=table,
6699        actions=[
6700            RenameColumn(this=old_column, to=new_column, exists=exists),
6701        ],
6702    )
6703
6704
6705def convert(value: t.Any, copy: bool = False) -> Expression:
6706    """Convert a python value into an expression object.
6707
6708    Raises an error if a conversion is not possible.
6709
6710    Args:
6711        value: A python object.
6712        copy: Whether to copy `value` (only applies to Expressions and collections).
6713
6714    Returns:
6715        Expression: the equivalent expression object.
6716    """
6717    if isinstance(value, Expression):
6718        return maybe_copy(value, copy)
6719    if isinstance(value, str):
6720        return Literal.string(value)
6721    if isinstance(value, bool):
6722        return Boolean(this=value)
6723    if value is None or (isinstance(value, float) and math.isnan(value)):
6724        return null()
6725    if isinstance(value, numbers.Number):
6726        return Literal.number(value)
6727    if isinstance(value, datetime.datetime):
6728        datetime_literal = Literal.string(
6729            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6730        )
6731        return TimeStrToTime(this=datetime_literal)
6732    if isinstance(value, datetime.date):
6733        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6734        return DateStrToDate(this=date_literal)
6735    if isinstance(value, tuple):
6736        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6737    if isinstance(value, list):
6738        return Array(expressions=[convert(v, copy=copy) for v in value])
6739    if isinstance(value, dict):
6740        return Map(
6741            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6742            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6743        )
6744    raise ValueError(f"Cannot convert {value}")
6745
6746
6747def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6748    """
6749    Replace children of an expression with the result of a lambda fun(child) -> exp.
6750    """
6751    for k, v in expression.args.items():
6752        is_list_arg = type(v) is list
6753
6754        child_nodes = v if is_list_arg else [v]
6755        new_child_nodes = []
6756
6757        for cn in child_nodes:
6758            if isinstance(cn, Expression):
6759                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6760                    new_child_nodes.append(child_node)
6761                    child_node.parent = expression
6762                    child_node.arg_key = k
6763            else:
6764                new_child_nodes.append(cn)
6765
6766        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6767
6768
6769def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6770    """
6771    Return all table names referenced through columns in an expression.
6772
6773    Example:
6774        >>> import sqlglot
6775        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6776        ['a', 'c']
6777
6778    Args:
6779        expression: expression to find table names.
6780        exclude: a table name to exclude
6781
6782    Returns:
6783        A list of unique names.
6784    """
6785    return {
6786        table
6787        for table in (column.table for column in expression.find_all(Column))
6788        if table and table != exclude
6789    }
6790
6791
6792def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6793    """Get the full name of a table as a string.
6794
6795    Args:
6796        table: Table expression node or string.
6797        dialect: The dialect to generate the table name for.
6798        identify: Determines when an identifier should be quoted. Possible values are:
6799            False (default): Never quote, except in cases where it's mandatory by the dialect.
6800            True: Always quote.
6801
6802    Examples:
6803        >>> from sqlglot import exp, parse_one
6804        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6805        'a.b.c'
6806
6807    Returns:
6808        The table name.
6809    """
6810
6811    table = maybe_parse(table, into=Table, dialect=dialect)
6812
6813    if not table:
6814        raise ValueError(f"Cannot parse {table}")
6815
6816    return ".".join(
6817        (
6818            part.sql(dialect=dialect, identify=True, copy=False)
6819            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6820            else part.name
6821        )
6822        for part in table.parts
6823    )
6824
6825
6826def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6827    """Returns a case normalized table name without quotes.
6828
6829    Args:
6830        table: the table to normalize
6831        dialect: the dialect to use for normalization rules
6832        copy: whether to copy the expression.
6833
6834    Examples:
6835        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6836        'A-B.c'
6837    """
6838    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6839
6840    return ".".join(
6841        p.name
6842        for p in normalize_identifiers(
6843            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6844        ).parts
6845    )
6846
6847
6848def replace_tables(
6849    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6850) -> E:
6851    """Replace all tables in expression according to the mapping.
6852
6853    Args:
6854        expression: expression node to be transformed and replaced.
6855        mapping: mapping of table names.
6856        dialect: the dialect of the mapping table
6857        copy: whether to copy the expression.
6858
6859    Examples:
6860        >>> from sqlglot import exp, parse_one
6861        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6862        'SELECT * FROM c /* a.b */'
6863
6864    Returns:
6865        The mapped expression.
6866    """
6867
6868    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6869
6870    def _replace_tables(node: Expression) -> Expression:
6871        if isinstance(node, Table):
6872            original = normalize_table_name(node, dialect=dialect)
6873            new_name = mapping.get(original)
6874
6875            if new_name:
6876                table = to_table(
6877                    new_name,
6878                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6879                    dialect=dialect,
6880                )
6881                table.add_comments([original])
6882                return table
6883        return node
6884
6885    return expression.transform(_replace_tables, copy=copy)
6886
6887
6888def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6889    """Replace placeholders in an expression.
6890
6891    Args:
6892        expression: expression node to be transformed and replaced.
6893        args: positional names that will substitute unnamed placeholders in the given order.
6894        kwargs: keyword arguments that will substitute named placeholders.
6895
6896    Examples:
6897        >>> from sqlglot import exp, parse_one
6898        >>> replace_placeholders(
6899        ...     parse_one("select * from :tbl where ? = ?"),
6900        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6901        ... ).sql()
6902        "SELECT * FROM foo WHERE str_col = 'b'"
6903
6904    Returns:
6905        The mapped expression.
6906    """
6907
6908    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6909        if isinstance(node, Placeholder):
6910            if node.name:
6911                new_name = kwargs.get(node.name)
6912                if new_name:
6913                    return convert(new_name)
6914            else:
6915                try:
6916                    return convert(next(args))
6917                except StopIteration:
6918                    pass
6919        return node
6920
6921    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6922
6923
6924def expand(
6925    expression: Expression,
6926    sources: t.Dict[str, Query],
6927    dialect: DialectType = None,
6928    copy: bool = True,
6929) -> Expression:
6930    """Transforms an expression by expanding all referenced sources into subqueries.
6931
6932    Examples:
6933        >>> from sqlglot import parse_one
6934        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6935        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6936
6937        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6938        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6939
6940    Args:
6941        expression: The expression to expand.
6942        sources: A dictionary of name to Queries.
6943        dialect: The dialect of the sources dict.
6944        copy: Whether to copy the expression during transformation. Defaults to True.
6945
6946    Returns:
6947        The transformed expression.
6948    """
6949    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
6950
6951    def _expand(node: Expression):
6952        if isinstance(node, Table):
6953            name = normalize_table_name(node, dialect=dialect)
6954            source = sources.get(name)
6955            if source:
6956                subquery = source.subquery(node.alias or name)
6957                subquery.comments = [f"source: {name}"]
6958                return subquery.transform(_expand, copy=False)
6959        return node
6960
6961    return expression.transform(_expand, copy=copy)
6962
6963
6964def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
6965    """
6966    Returns a Func expression.
6967
6968    Examples:
6969        >>> func("abs", 5).sql()
6970        'ABS(5)'
6971
6972        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6973        'CAST(5 AS DOUBLE)'
6974
6975    Args:
6976        name: the name of the function to build.
6977        args: the args used to instantiate the function of interest.
6978        copy: whether to copy the argument expressions.
6979        dialect: the source dialect.
6980        kwargs: the kwargs used to instantiate the function of interest.
6981
6982    Note:
6983        The arguments `args` and `kwargs` are mutually exclusive.
6984
6985    Returns:
6986        An instance of the function of interest, or an anonymous function, if `name` doesn't
6987        correspond to an existing `sqlglot.expressions.Func` class.
6988    """
6989    if args and kwargs:
6990        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6991
6992    from sqlglot.dialects.dialect import Dialect
6993
6994    dialect = Dialect.get_or_raise(dialect)
6995
6996    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
6997    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
6998
6999    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7000    if constructor:
7001        if converted:
7002            if "dialect" in constructor.__code__.co_varnames:
7003                function = constructor(converted, dialect=dialect)
7004            else:
7005                function = constructor(converted)
7006        elif constructor.__name__ == "from_arg_list":
7007            function = constructor.__self__(**kwargs)  # type: ignore
7008        else:
7009            constructor = FUNCTION_BY_NAME.get(name.upper())
7010            if constructor:
7011                function = constructor(**kwargs)
7012            else:
7013                raise ValueError(
7014                    f"Unable to convert '{name}' into a Func. Either manually construct "
7015                    "the Func expression of interest or parse the function call."
7016                )
7017    else:
7018        kwargs = kwargs or {"expressions": converted}
7019        function = Anonymous(this=name, **kwargs)
7020
7021    for error_message in function.error_messages(converted):
7022        raise ValueError(error_message)
7023
7024    return function
7025
7026
7027def case(
7028    expression: t.Optional[ExpOrStr] = None,
7029    **opts,
7030) -> Case:
7031    """
7032    Initialize a CASE statement.
7033
7034    Example:
7035        case().when("a = 1", "foo").else_("bar")
7036
7037    Args:
7038        expression: Optionally, the input expression (not all dialects support this)
7039        **opts: Extra keyword arguments for parsing `expression`
7040    """
7041    if expression is not None:
7042        this = maybe_parse(expression, **opts)
7043    else:
7044        this = None
7045    return Case(this=this, ifs=[])
7046
7047
7048def cast_unless(
7049    expression: ExpOrStr,
7050    to: DATA_TYPE,
7051    *types: DATA_TYPE,
7052    **opts: t.Any,
7053) -> Expression | Cast:
7054    """
7055    Cast an expression to a data type unless it is a specified type.
7056
7057    Args:
7058        expression: The expression to cast.
7059        to: The data type to cast to.
7060        **types: The types to exclude from casting.
7061        **opts: Extra keyword arguments for parsing `expression`
7062    """
7063    expr = maybe_parse(expression, **opts)
7064    if expr.is_type(*types):
7065        return expr
7066    return cast(expr, to, **opts)
7067
7068
7069def array(
7070    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7071) -> Array:
7072    """
7073    Returns an array.
7074
7075    Examples:
7076        >>> array(1, 'x').sql()
7077        'ARRAY(1, x)'
7078
7079    Args:
7080        expressions: the expressions to add to the array.
7081        copy: whether to copy the argument expressions.
7082        dialect: the source dialect.
7083        kwargs: the kwargs used to instantiate the function of interest.
7084
7085    Returns:
7086        An array expression.
7087    """
7088    return Array(
7089        expressions=[
7090            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7091            for expression in expressions
7092        ]
7093    )
7094
7095
7096def tuple_(
7097    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7098) -> Tuple:
7099    """
7100    Returns an tuple.
7101
7102    Examples:
7103        >>> tuple_(1, 'x').sql()
7104        '(1, x)'
7105
7106    Args:
7107        expressions: the expressions to add to the tuple.
7108        copy: whether to copy the argument expressions.
7109        dialect: the source dialect.
7110        kwargs: the kwargs used to instantiate the function of interest.
7111
7112    Returns:
7113        A tuple expression.
7114    """
7115    return Tuple(
7116        expressions=[
7117            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7118            for expression in expressions
7119        ]
7120    )
7121
7122
7123def true() -> Boolean:
7124    """
7125    Returns a true Boolean expression.
7126    """
7127    return Boolean(this=True)
7128
7129
7130def false() -> Boolean:
7131    """
7132    Returns a false Boolean expression.
7133    """
7134    return Boolean(this=False)
7135
7136
7137def null() -> Null:
7138    """
7139    Returns a Null expression.
7140    """
7141    return Null()